left join in TransactSQL returning me unexpected result - is my understanding for left join wrong?

Question

Here is my data

CREATE TABLE TempA (  
  ID  INT IDENTITY(1,1),  
  Msg  VARCHAR(20)  
)  

INSERT INTO TempA (Msg) values ( 'a')  
INSERT INTO TempA (Msg) values ( 'b')  
INSERT INTO TempA (Msg) values ( 'c')  

CREATE TABLE TempB (  
  ID  INT IDENTITY(1,1),  
  Msg  VARCHAR(20)  
)

So TempB is empty. Now I run following query

   select a.* 
     from TempA a 
left JOIN TempA B on a.id = b.id

It returns 3 rows from TempA as expected, good so far. Let's add a filter in query above

   select a.* 
     from TempA a 
left JOIN TempA B on a.id = b.id  
    where b.msg = 'aa'

It return no rows to me. I thought that since its a left join, i should still get 3 rows from TempA table. Am I wrong?

Note: all the example queries are self-joins. I think you mean for the second table to be "TempB". That would explain why the answers below aren't working. — Bill, Jan 14 '11 at 19:48

Martin Smith · Accepted Answer · 2011-01-14T19:45:36.347

4

You are converting the left join to an inner join by including the b.msg filter in the where clause use.

select a.* from TempA a left JOIN TempB B on a.id = b.id and b.msg = 'aa'

(Conceptually) the Join Predicate happens, the non joining rows from A get added back in and will have a value of NULL for b.msg then you exclude these rows again with the filter!

You might want to review Itzik Ben Gan's Logical Query Processing Poster

edited Jan 14 '11 at 19:45

answered Jan 14 '11 at 19:29

Martin Smith

438,706
87
741
845

can you please elaborate a bit how is it been converted to an inner join? – imak Jan 14 '11 at 19:33
your where condition `b.msg='aa'` can never be true for any rows that were output by the join with all the `b` columns null because the `on` condition did not match; so the join is *effectively* acting as an inner join. – araqnid Jan 14 '11 at 19:34
Think of it this way, the FROM clause, with all its joins, determines an initial data set. Then the WHERE clause filters stuff out. If you put the filter in the LEFT JOIN, you will get all rows from "A", along with whatever "B" records match the join condition. Try including b.msg in your Select. In your example, they will all be NULLs. Now move the condition to the WHERE clause. All the records with b.msg=NULL (because no match) get filtered out. – Bill Jan 14 '11 at 19:46

score 4 · Answer 2 · answered Jan 14 '11 at 19:35

Here's what's going on;

You have two tables.

TempA ID Msg
      -- ---
      1  a
      2  b
      3  c

TempB ID Msg
      -- ---

Now, when you do the join, the initial result looks like this:

Result a.ID a.Msg b.ID b.Msg
       ---- ----- ---- -----
       1    a     NULL NULL
       2    b     NULL NULL
       3    c     NULL NULL

When you filter that query with the WHERE clause, you're filtering out anything that doesn't have a b.Msg of 'aa'. That would filter out all the records, because all of them have a b.Msg of NULL. Leaving you with this:

Result a.ID a.Msg b.ID b.Msg
       ---- ----- ---- -----

Then, you're only selecting the columns from TempA which makes this the final result:

Result a.ID a.Msg
       ---- -----

nice, but then the solution @Conrd provided should work too. How come that didn't work? — imak, Jan 14 '11 at 19:40
@imak: probably because you keep joining `TempA` with itself - `TempA` again, under the `B` alias..... you probably really want to join to `TempB B`.... — marc_s, Jan 14 '11 at 20:00

score 2 · Answer 3 · answered Jan 14 '11 at 19:33

2

When using an OUTER JOIN, criteria provided in the WHERE clause is applied after the JOIN. Because there are no rows where b.msg = 'aa', no rows will be returned.

When the criteria is specified in the ON clause, the criteria is applied before the JOIN, so references to b will only be affected. In this example, b references will return NULL while the a references are unaffected.

answered Jan 14 '11 at 19:33

OMG Ponies

325,700
82
523
502

is this rule of WHERE been applied after join is also true in case of INNER JOINs? – imak Jan 14 '11 at 19:41
@imak: There's no difference when using INNER JOINs, only OUTER JOINs. – OMG Ponies Jan 14 '11 at 19:43

Conrad Frix · Answer 4 · 2011-01-14T20:11:51.137

1

I perfer Matin's solution but the other option is

select a.* 
from 
    TempA a 
    left JOIN TempB B on a.id = b.id
where b.msg = 'aa' or b.msg is null

edited Jan 14 '11 at 20:11

answered Jan 14 '11 at 19:31

Conrad Frix

51,984
12
96
155

this doesn't give me any results either – imak Jan 14 '11 at 19:35
@imak - Your original query had TempA where TempB should have been. This returns results for the DDL in your question `select a.* from TempA a left JOIN TempB b on a.ID = b.ID where b.Msg = 'aa' or b.Msg is null` – Martin Smith Jan 14 '11 at 19:47
@imak: probably because you keep joining `TempA` with itself - `TempA` again, under the `B` alias..... you probably really want to join to `TempB B`.... – marc_s Jan 14 '11 at 19:52
@Martin. That's what I get for copying and pasting without looking too closely. I've updated it Thanks – Conrad Frix Jan 14 '11 at 20:12

score 0 · Answer 5 · edited May 23 '17 at 12:04

0

It depends on how you do the filtering.
I answered a similar question a few months ago. Check out my explanation about left joins there:

What is the difference in these two queries as getting two different result set?

edited May 23 '17 at 12:04

Community

1
1

answered Jan 14 '11 at 19:50

Christian Specht

35,843
15
128
182

left join in TransactSQL returning me unexpected result - is my understanding for left join wrong?

5 Answers5