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

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?

Answers


create table A(id int);
create table B(id int);

INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);

INSERT INTO B VALUES(1);
INSERT INTO B VALUES(2);
INSERT INTO B VALUES(3);

SELECT * FROM A;
SELECT * FROM B;

id
-----------
1
2
3

id
-----------
1
2
3

Filter on the JOIN to prevent rows from being added during the JOIN process.

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

id          id
----------- -----------
1           NULL
2           2
3           NULL

WHERE will filter after the JOIN has occurred.

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

id          id
----------- -----------
2           2

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    |
+------+------+

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.


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.


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


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.


Need Your Help

How to capture a git commit message and run an action

git bash githooks

I'm new to git and I want to be able to capture the commit message after a push to the origin/master and run a bash script (on the server) based on what the string contains.

AngularJS - How do you convert milliseconds to xHours and yMins

javascript angularjs date-format date-formatting

I've a requirement where I want to convert milliseconds to xHours and yMins in AngularJS.