+ 2

Difference between WHERE and ON in a join

What is the difference in WHERE and ON keyword while doing a join?

7th Mar 2017, 11:16 AM
Prateek Tewari
Prateek Tewari - avatar
8 Réponses
+ 3
It make no difference for INNER JOINs, they are interchangeable. However, it does matter for OUTER JOINs. WHERE filters the data after the join has been made whereas ON filters the data before the join is made.
7th Mar 2017, 12:09 PM
Anthony Vanover
Anthony Vanover - avatar
+ 2
@Prateek The result might happen to be the same, but you cannot count on it.
8th Mar 2017, 8:10 AM
Anthony Vanover
Anthony Vanover - avatar
+ 1
They are not the same thing. Consider these queries: SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID WHERE Orders.ID = 12345 and SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID AND Orders.ID = 12345 The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it. With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.
8th Mar 2017, 12:05 PM
Akwin Lopez
Akwin Lopez - avatar
+ 1
The query optimizer is smart enough to decide. The presence of the filter condition in JOIN or in WHERE clause would have no effect. The execution will remain the same and neither performance would deteriorate nor increase. So both queries in your example would run the same way. I personally use the filter condition in WHERE clause for better readability. But when it comes to OUTER JOINs, it is slightly different. The core logic of the query can change If you apply the filter in WHERE clause OR JOIN clause, the join can no longer remain an outer join and could become an inner join in case of LEFT/RIGHT outer join OR become left/right outer join in case of FULL OUTER JOIN
8th Mar 2017, 12:07 PM
Akwin Lopez
Akwin Lopez - avatar
0
@Anthony are we getting the same result by both in case of an outer join?
7th Mar 2017, 3:13 PM
Prateek Tewari
Prateek Tewari - avatar
0
Where will only filter data AFTER the join has been made, which will only take effect on an outer join.
7th Mar 2017, 3:14 PM
Damon Smithies
Damon Smithies - avatar
0
@Damon Has it something to do with the latency in the result?
7th Mar 2017, 3:15 PM
Prateek Tewari
Prateek Tewari - avatar
0
The where clause applies to the whole resultset; the on clause only applies to the join in question.
8th Mar 2017, 12:08 PM
Akwin Lopez
Akwin Lopez - avatar