LEFT JOIN Predicates ON vs WHERE

Question

LEFT JOIN Predicates ON vs WHERE

Ray Dai

SSCommitted

Points: 1548
More actions
February 16, 2010 at 7:42 pm

#141293

I am doing left joins where I am trying to find values that are in the left table but not in the right table, like a NOT IN. Simple enough except I am also looking for specific values besides the joining key. In other words, I am looking for Lookup keys for which there is certain value in the LEFT but not certain values in the RIGHT. Whether I put the predicates on "ON" or "WHERE" made a huge difference.
To prepare the DB:
create table #A (Lookup int, Val varchar(10))
create table #B (Lookup int, Val varchar(10))
insert into #A values (1, 'A')
insert into #A values (2, 'B')
insert into #A values (3, 'C')
insert into #B values (2, 'B')
insert into #B values (3, 'C')
insert into #B values (4, 'D')
insert into #B values (1, 'E')
Then these three SELECTS trying to find Lookup values for which there is a "B" in the LEFT
but no "E" in the right.
select * --this is correct
from #A
LEFT join #B
on #A.Lookup=#B.Lookup
and #B.Val in ('E')
WHERE #B.Lookup is null
and #A.Val = 'B'
select * --this returns nothing
from #A
LEFT join #B
on #A.Lookup=#B.Lookup
WHERE #B.Lookup is null
and #A.Val = 'B'
and #B.Val in ('E')
select * --this returns everything
from #A
LEFT join #B
on #A.Lookup=#B.Lookup
and #A.Val = 'B'
and #B.Val in ('E')
WHERE #B.Lookup is null
Syntax-wise they look very similar but the results are very different and only the 1st one is correct. The explain plan is also very different. I researched this and it seems it has something to do with the order with which SQL Server parse the ON vs WHERE clause.
It seems like to get what I want, I need to have all the predicates for the RIGHT table in the "ON" clause and have the RightTable.ID is null and all the predicates for the LEFT table in the "WHERE" clause.
Can someone explain this behavior, using my data, for all three queries included?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Putting predicates for an outer-joined table in the WHERE clause will make it effectively inner-joined.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ray Dai SSCommitted Points: 1548 More actions · Answer 2

Thanks, that explains case #2 very well. Any suggestions on #3?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

on #3, the thing to look at closely is how Books online defines a LEFT OUTER JOIN. Per BOL:

Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

So:

- records from #A that match the JOIN criteria are included with the corresponding values from B

- ALL OTHER values from #A are included, but with the columns from B all NULL

So, since there are NO results where a row from #A with a value of 'B' matches a value in #b with a value of 'E', all rows from #A are included based on the ALL OTHER rule listed above.

In other words - you cannot use join criteria to filter the "Left" side of a LEFT OUTER JOIN.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ray Dai SSCommitted Points: 1548 More actions · Answer 4

Thank you all for your help. This makes sense. I guess I just never looked things up 😀

Also doing only INNER (where you put the predicate didn't make a difference) and LEFT joins with no further predicates for the RIGHT table, this problem never happened before.

Paul White SSC Guru Points: 150467 More actions · Answer 5

I hope you now understand why my reply did answer your question on the other thread 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ray Dai SSCommitted Points: 1548 More actions · Answer 6

Paul,

I DEFINITELY appreciate your help as well. I guess the other explanations made more sense. I need examples! 😀