Using AND inside an INNER JOIN can?

Asked

Viewed 1,943 times

5

I wonder if it is possible to use AND within a INNER JOIN, example:

SELECT *
  FROM tab1
 INNER JOIN tab2
    ON tab2.id2 = tab1.id1
   AND tab2.camp1 = 'valor'
 INNER JOIN tab3
    ON tab3.id3 = tab2.id2;

I gave a very silly example, but the question is whether you have any way of using the AND after the ON and before another INNER JOIN?

mysql sql database select join

2 answers

6

Yes you can, but it is the case to use it only when the key is composed. The restriction of results should be made in the WHERE.

5


Yes it is possible to have one or more clases JOIN composed with operators as AND, OR <> etc. Equi Join is more common even.

Basically the composite Join already discards records that do not meet the criteria at the junction. If you have a 'simple' Join and later a WHERE means the disposal will be in the restriction, or is first made is the junction and then the disposal.

The 'disposal' done in the JOIN (junction)

SELECT * FROM t1
INNER JOIN t2 ON t1.id = t2.id AND td2.data = '2017-02-10'

The 'disposal' done in the WHERE (restriction)

SELECT * FROM t1 
INNER JOIN t2 ON t1.id = t2.id
WHERE td2.data = '2017-02-10'

Browser other questions tagged mysql sql database select join

You are not signed in. Login or sign up in order to post.