# how does order of and matter in a query with join clause

 0 I found different results using below queries SELECT A..COLA, B.COLB FROM TABLEA A INNER JOIN TABLEB B ON B.COLA = A.COLA LEFT OUTER JOIN TABLEC C ON C.COLB = B.COLB AND B.COLC IN ('','Y','O') WHERE A.COLD = 'XYZ' SELECT A..COLA, B.COLB FROM TABLEA A INNER JOIN TABLEB B ON B.COLA = A.COLA AND B.COLC IN ('','Y','O') LEFT OUTER JOIN TABLEC C ON C.COLB = B.COLB WHERE A.COLD = 'XYZ' I need to understand why does position of and matter when it refers to a specific table - in this case TABLEB. In the first query the output does not consider AND operator but in the second query. Thanks. more ▼ asked Aug 15, 2012 at 01:54 PM in Default visrah 40 ● 1 ● 1 ● 2 Fatherjack ♦♦ 42.8k ● 75 ● 79 ● 108 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 In the first you are saying INNER JOIN TABLEB B ON B.COLA = A.COLA LEFT OUTER JOIN TABLEC C ON C.COLB = B.COLB AND B.COLC IN ('','Y','O') and in the second INNER JOIN TABLEB B ON B.COLA = A.COLA AND B.COLC IN ('','Y','O') LEFT OUTER JOIN TABLEC C ON C.COLB = B.COLB So, firstly rows are filtered by the join between C and B where the columns match AND B.COLC has to be in the options whereas in the last option returns rows where the join on B and A matches AND B.COLC is in the list of options.These are two very different queries. more ▼ answered Aug 15, 2012 at 02:01 PM Fatherjack ♦♦ 42.8k ● 75 ● 79 ● 108 Jonathan thanks for the answer but i didn't quite follow your explanation. Do you think you/someone can explain me with an example (with data) so that it would be easy for me to understand?I appreciate your help. Aug 15, 2012 at 05:17 PM visrah add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 Another example may drive the point home. When using outer joins you need to be mindful of what is in the JOIN condition and what is in the WHERE clause. With INNER joins this is not an issue: CREATE TABLE #TableA([ColA] [int],[ColB] [int]) CREATE TABLE #TableB([ColA] [int],[ColB] [int]) INSERT #TableA VALUES(1,1),(1,2),(1,3) INSERT #TableB VALUES(1,1),(1,2) -- using LEFT joins ----------------------- SELECT * FROM #TableA ta LEFT JOIN #TableB tb ON (ta.ColA = tb.ColA AND ta.ColB = tb.ColB) /* NOT equivalent to */ SELECT * FROM #TableA ta LEFT JOIN #TableB tb ON (ta.ColA = tb.ColA) WHERE ta.ColB = tb.ColB -- using INNER joins ----------------------- SELECT * FROM #TableA ta JOIN #TableB tb ON (ta.ColA = tb.ColA AND ta.ColB = tb.ColB) /* IS equivalent to */ SELECT * FROM #TableA ta JOIN #TableB tb ON (ta.ColA = tb.ColA) WHERE ta.ColB = tb.ColB DROP TABLE #TableA, #TableB more ▼ answered Aug 15, 2012 at 11:50 PM Scot Hauder 6.1k ● 13 ● 15 ● 18 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x122
x19

asked: Aug 15, 2012 at 01:54 PM

Seen: 1362 times

Last Updated: Aug 16, 2012 at 10:30 AM