What is the difference between On or Where in a LEFT JOIN?

Question

What is the difference between the following snippets of SQL code:

select count(*) from A left join B on a.id = b.id where a.status = 2 and b.id is NULL 

and

 select count(*) from A left join B on a.id = b.id and a.status =2 where b.id is NULL 

? I read this: The semantic difference between join requests , but I still don't know which one is better to use

I spent the past hour reading this and understood all the answers to the value added, but I understood the code example along with "never encountered this" the best

Answer 1

By rewriting 2 queries without LEFT JOIN / IS NULL , it is therefore obvious that in some cases they can return different data sets:

 ---Query 1 SELECT COUNT(*) FROM a WHERE a.status = 2 AND NOT EXISTS ( SELECT * FROM b WHERE a.id = b.id ) 

and

 ---Query 2 SELECT COUNT(*) FROM a WHERE NOT ( a.status = 2 AND EXISTS ( SELECT * FROM b WHERE a.id = b.id ) ) --- or: ---Query 2 SELECT COUNT(*) FROM a WHERE a.status <> 2 OR NOT EXISTS ( SELECT * FROM b WHERE a.id = b.id ) 

Answer 2

This is the key to how LEFT connections are used, one will filter the result, the other will simply terminate the failed LEFT connection, saving the data from the LEFT from JOIN.

(1) left junction B on a.id = b.id , where a.status = 2

Ignoring other filters, this indicates a LEFT JOIN with respect to table B, so "try joining table B using condition a.id=b.id ".
If you cannot make a match, save the entries in the left table (i.e. A). After that, on the remaining records, FILTER out (i.e. REMOVE) records are written that do not correspond to a.status=2

(2) left junction B on a.id = b.id and a.status = 2

Ignoring the other filters, this means that LEFT JOIN is against table B in two conditions, so "try to join table B in both conditions a.id = b.id and a.status =2 ". If you do not have entries from B that give both conditions (even if they are not related to B), save the entry from A anyway.

Answer 3

The difference is that the condition is logically evaluated, which in turn can affect the result set.

In your examples (reformatted) you have:

Example 1

 SELECT COUNT(*) FROM A LEFT JOIN B ON a.id = b.id WHERE a.status = 2 AND b.id is NULL 

Example 2

 SELECT COUNT(*) FROM A LEFT JOIN B ON a.id = b.id AND a.status = 2 WHERE b.id is NULL 

In the first case, a LEFT JOIN is applied and a result set is created; it is then filtered with two conditions in the WHERE clause.

In the second case, the LEFT JOIN is formed with the filtering condition on a.status and in some cases can change the result set from the LEFT JOIN. This result set is then filtered with the main WHERE clause.

Example 2 is essentially equivalent:

Example 2A

 SELECT COUNT(*) FROM (SELECT * FROM A WHERE a.status = 2) AS A LEFT JOIN B ON a.id = b.id WHERE b.id is NULL 

With some queries (but probably not with this) the difference can make a difference.

---

Try creating some simple sample data:

 Table A Table B id status id 4 2 1 5 3 

Example 1 will have an intermediate result set:

 a.id a.status b.id 4 2 null 5 3 null 

and the WHERE clause excludes the second line.

Example 2 will have an intermediate result set:

 a.id a.status b.id 4 2 null 

In this example, the net result is the same, and I could not find data that did not match.

If the query condition that is moving is in an outer joined table and is more complex than simple equality, then you can see the effect.

Answer 4

Perhaps this is easier:

 SELECT id FROM A WHERE status = 2 EXCEPT SELECT id FROM B; 

In the unlikely event, you are only interested in the power of the result:

 SELECT COUNT(*) FROM ( SELECT id FROM A WHERE status = 2 EXCEPT SELECT id FROM B ) AS DT1;