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  VARC开发者_高级运维HAR(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?

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

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

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.

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

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?