Difference between "on .. and" and "on .. where" in SQL Left Join? [duplicate]

Question

Sql statement.

1.select a.* from A a left join B b on a.id =b.id and a.id=2;

2.select a.* from A a left join B b on a.id =b.id where a.id=2;

what is the difference of this two sql statement?

Answer 1

select a.* from A a left join B b on a.id =b.id and a.id=2;

This only uses a.id in the join condition, so records where a.id <> 2 don't get filtered out. You might get a result like this:

+------+------+
| a.id | b.id |
+------+------+
| 1    | NULL |
| 2    | 2    |
| 3    | NULL |
+------+------+

You don't select any of b's columns, but if you do, it'll be easier to understand.

select a.* from A a left join B b on a.id =b.id where a.id=2;

Now records where a.id <> 2 do get filtered out.

+------+------+
| a.id | b.id |
+------+------+
| 2    | 2    |
+------+------+

Answer 2

As @hvd says, the "where" clause filters rows returned by the join, so the "where" version won't return outer-joined rows (which have a.id = null).

However there is another significant difference: Even if the outer joined rows were not filtered out, there can be a massive performance boost putting the condition into the "on" clause, because the result set is made smaller earlier.

This is particularly pronounced when a series of other left joined tables follows the one with the "and" condition - you can prevent joins from even happening to the following tables for unsuitable rows and potentially chop off millions of rows from reaching the filtering ("where") stage.

Answer 3

I try some time ,and I know what is the reason, it only related to a priority.

select * from A a left join B b on a.id=b.id and b.id=2

this means A left join (where b.id=2) this is the condition filter B first

Select *?from A a left join B b on a.id=b.id where a.id=2

this means after join B ,then filter by a.id=2

Answer 4

If you think about the syntax of a SQL query, the 'AND' extends the join block (as if where parenthesis) where as the 'WHERE' defines the start of the WHERE/filtering block of the query.

Answer 5

As clearly explained by the @mr_eclair

what happens in both cases. Let me tell you an easy way to remember this.

select a.*,b.*
from   A a left join B b 
**on**     a.id =b.id ***and*** a.id=2;

Here the "AND" worked on the "ON" and it provides a condition to the joining criteria.

select a.*,b.* 
from   A a left join B b 
on     a.id =b.id 
**where**  a.id=2;

whereas here "WHERE" provided a condition to all the result.

To put it more clearly, "WHERE" filter out the result set after finding the result from "SELECT" statement. "AND" is a condition on joining the two tables.