Semantic difference between join queries

Question

I have two queries that I thought meant the same thing, but I keep getting different results and I was hoping someone could explain how these are different:

1.

select * 
from table1 a 
left join table2 b on a.Id = b.Id and a.val = 0
where b.Id is null

2.

select * 
from table1 a 
left join table2 b on a.Id = b.Id 
where b.Id is null 
    and a.val = 0

The point of the query is to find the rows that are in table1 and val = 0 that are not in table2.

I'm using sql server 2008 as well, but I doubt that this should matter.

Answer 1

  SELECT * from TABLE1 t1
  WHERE Val = 0
  AND NOT EXISTS(SELEct 1 from Table2 t2 Where t1.Id = t2.Id)

Answer 2

When considering left joins think of them as having 3 conceptual stages.

1. The join filter is applied
2. The left rows are added back in
3. the where clause is applied.

You will then see why you get different results.

That also explains why this returns results

select o.* 
from sys.objects o
left join sys.objects o2 on o.object_id=o2.object_id and 1=0

And this doesn't.

select o.* 
from sys.objects o
left join sys.objects o2 on o.object_id=o2.object_id 
where 1=0

Answer 3

If you remove the WHERE clause entirely, using a LEFT OUTER JOIN means that all the rows from the table on the left hand side will appear, even if they don't satisfy the JOIN criteria. For example, no rows satisfy the expression 1 = 0 however this:

SELECT * 
  FROM table1 AS a 
       LEFT OUTER JOIN table2 AS b 
          ON a.Id = b.Id
             AND 1 = 0;

still results in all rows in table1 being returned. Simply put, that's the way OUTER JOINs work.

The WHERE clause is applied after the JOIN, therefore this

SELECT * 
  FROM table1 AS a 
       LEFT OUTER JOIN table2 AS b 
          ON a.Id = b.Id
 WHERE 1 = 0;

will return no rows.