CODEWITHSUNDEEP
sqlsql-servert-sql
imak
imak

Reputation: 6689

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

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?

Upvotes: 1

Views: 311

Answers (5)

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 1

Christian Specht
Christian Specht

Reputation: 36421

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?

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452988

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

Upvotes: 4

Adam Maras
Adam Maras

Reputation: 26853

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

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332531

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.

Upvotes: 2

Related Questions