Difference between WHERE and ON in a join

Question

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

Answer 1

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.

Answer 2

@Prateek The result might happen to be the same, but you cannot count on it.

Answer 3

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.

Answer 4

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

Answer 5

@Anthony are we getting the same result by both in case of an outer join?

Answer 6

Where will only filter data AFTER the join has been made, which will only take effect on an outer join.

Answer 7

@Damon Has it something to do with the latency in the result?

Answer 8

The where clause applies to the whole resultset; the on clause only applies to the join in question.