Semantic difference between join requests

Question

I have two questions that I thought mean the same thing, but I have different results, and I was hoping someone could explain how they differ:

one.

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 query point is to find the rows that are in table1 and val = 0 that are not listed in table2.

I also use SQL Server 2008, but I doubt it matters.

Answer 1

When considering left associations, they are thought of as three conceptual stages.

• Connection filter applied
• Left lines are added back to
• where clause is applied.

Then you will see why you get different results.

It also explains why it 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 is not so.

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

Answer 2

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

Answer 3

If you completely exclude the WHERE , using the LEFT OUTER JOIN means that all rows from the table will appear on the left side, even if they do not meet the JOIN criteria. For example, no lines satisfy the expression 1 = 0 , but this:

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

still returns all rows in table1 where id values ​​match. Simply put, OUTER JOIN works.

The WHERE applies after JOIN , so this

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

will not return rows.