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

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?

http://stackoverflow.com/questions/10297231/where-clause-vs-on-when-using-join — Habib, Sep 13 '12 at 06:33
Not a duplicate, having `LEFT JOIN` here significantly changes the question. — , Sep 13 '12 at 06:36
@hvd - the questions may not be exact duplicates, but since most of the answers are along the lines of "it doesn't matter for `INNER JOIN`, but here's what would be different for `OUTER JOIN`s..." — Damien_The_Unbeliever, Sep 13 '12 at 06:50
@Damien_The_Unbeliever is right, this question is duplicated from other questions that have already been accurately answered — Yaroslav, Sep 13 '12 at 07:04
@Damien_The_Unbeliever Closing as a duplicate is what you should do when the *question* is a duplicate, not when an answer to a different question happens to also answer this one. See [the FAQ](http://stackoverflow.com/faq#close): **exact duplicate** This question covers exactly the same content as earlier questions on this topic — , Sep 13 '12 at 08:50

score 42 · Accepted Answer · answered Sep 13 '12 at 07:05

42

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

answered Sep 13 '12 at 07:05

Vishwanath Dalvi

33,724
39
121
151

Thanks for your detail explain – jack.li Sep 13 '12 at 07:32
For some reason it didn't work as expected in this sqlfiddle: http://sqlfiddle.com/#!2/9684d/4 But when I tried it in MySQL it worked exactly as you said. – Buttle Butkus May 23 '13 at 04:03
Would it be correct to say that adding ' and a.id=2' does not bring any difference to query and makes no sense? So we can safely just skip it and use LEFT JOIN – Andrey M. Stepanov Feb 03 '19 at 12:31

score 12 · Answer 2 · edited Feb 09 '22 at 23:02

12

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

edited Feb 09 '22 at 23:02

Audwin Oyong

2,061
3
10
31

answered Sep 13 '12 at 06:33

Is select a.* from A a left join B b on a.id =b.id and a.id=2; equivalent to select a.* from A a left join B b on a.id =b.id ? – Andrey M. Stepanov Sep 12 '18 at 16:24
@Andrey No, even though no columns from `b` are selected, a successful join can cause `a`'s records to be duplicated in the result set. – Sep 12 '18 at 16:39

score 0 · Answer 3 · edited May 23 '17 at 11:33

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.

score 0 · Answer 4 · answered Sep 14 '12 at 01:14

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

score 0 · Answer 5 · answered Sep 14 '12 at 01:27

0

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.

answered Sep 14 '12 at 01:27

Babak Naffas

11,988
3
35
49

score 0 · Answer 6 · answered Nov 26 '18 at 05:07

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.

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

6 Answers6

Linked

Related