question

dhoffmeester avatar image
dhoffmeester asked

Is there any danger in using 2 tables in a join ... on clause

-- Is there any danger in doing the following to find missing entries in T3 CREATE table TABLE1 ( id int, DESCR varchar(10) ) CREATE table TABLE2 ( id int, code varchar(10) ) CREATE table TABLE3 ( id int, [DESCR] varchar(10), code varchar(10) ) SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id LEFT JOIN table3 t3 ON t1.id = t3.id AND t2.code = t3.code WHERE t3.id IS NULL /* I know it works but is there any danger in using the above I have tested it and cannot find issue with it Is it just safer to use the code below? */ SELECT * FROM ( SELECT t1.id, t1.DESCR, t2.code FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id) t12 LEFT JOIN table3 t3 ON t12.id = t3.id AND t12.code = t3.code WHERE t3.id IS NULL
selectinner joincodeleft joinon
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
David Wimbush avatar image
David Wimbush answered
I think your first version is fine. The parentheses in the second version don't change the query or add anything for the human reader's benefit.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.