CODEWITHSUNDEEP
sqlsql-serversql-server-2008left-join
chuck taylor
chuck taylor

Reputation: 2516

Semantic difference between join queries

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.

Upvotes: 3

Views: 483

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

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 where the id values match. 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.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453277

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

Upvotes: 9

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

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

Upvotes: 1

Related Questions