What is the difference between putting a condition in the JOIN's ON clause versus the WHERE clause?
Quick Answer
For an INNER JOIN it makes no difference — filtering in ON or WHERE gives the same result. For an OUTER JOIN it matters a lot: a condition on the outer (right) table in the WHERE clause turns the LEFT JOIN into an INNER JOIN (the NULL rows fail the test); put it in the ON clause to filter which rows join while keeping the unmatched left rows.
With an inner join, a filter in the ON clause and the same filter in the WHERE clause produce identical results — the optimizer treats them the same. With an outer join it's completely different. If I have a LEFT JOIN and I put o.Status = 'Open' in the WHERE clause, the customers with no orders have a NULL status, that fails the comparison, and they drop out — I've silently made it an inner join. To filter which orders join while keeping every customer, that condition goes in the ON clause.
LEFT JOIN Orders o ON o.CustomerId = c.Id AND o.Status = 'Open'→ all customers, with their open orders or NULLs.LEFT JOIN Orders o ON o.CustomerId = c.Id WHERE o.Status = 'Open'→ only customers who have an open order.WHERE o.CustomerId IS NULLis legitimately in WHERE — that's the anti-join pattern, applied after the join.