x

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

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 gravatar image

visrah
40 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The point that both @Fatherjack and @Scot Hauder are trying to make is that an INNER JOIN uses the join predicates to actively filter out the entire result set. The matching data from both sides of the join will be returned and nothing else.

An OUTER JOIN on the other hand takes the data from the "driving" table (TableA in your example) and joins to TableB using the join predicate you specify and tries to collect matching data for the entire selected set of TableA. If there is no corresponding data in TableB you get NULLs returned in place of data for TableB.

If you add additional ANDs to an INNER JOIN it doesn't matter if they are in the JOIN or in the WHERE section of the query. They equate to the same filtering operation in the end so the Query Optimizer will generate the same plan for both ways.

If you add additional ANDs to an OUTER JOIN, placing it in the JOIN will change how the outer table (TableB) is joined and probably just increase the amount of NULLs produced. If you move the same AND down to the WHERE clause, you will find that your result set will be filtered according to the entire WHERE clause and end up with less rows than if you had the AND in the JOIN.
more ▼

answered Aug 16, 2012 at 06:50 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+1. I think you nailed the explaination.
Aug 16, 2012 at 10:30 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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 gravatar image

Fatherjack ♦♦
42.4k 75 78 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
(comments are locked)
10|1200 characters needed characters left

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 gravatar image

Scot Hauder
6k 13 15 18

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x985
x116
x17

asked: Aug 15, 2012 at 01:54 PM

Seen: 1226 times

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