The ON clause defines how rows match, while the WHERE clause filters the result after JOIN processing. With OUTER JOINs, moving a condition between them can change the meaning.
Incorrect filter placement is a common reason LEFT JOIN queries unexpectedly behave like INNER JOINs.
Keep all customers but only match orders from 2026.
SELECT
c.CustomerName,
o.OrderID,
o.OrderDate
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
AND o.OrderDate >= '2026-01-01';Used in customer reporting, transaction analysis, dashboards, and complex data extraction.
- ON controls matching.
- WHERE filters the final result.
- Moving an OUTER JOIN condition into WHERE can remove unmatched rows.
- Filter placement should reflect the business requirement.
- Always test LEFT JOIN behavior with NULL cases.
What is the difference between ON and WHERE in a JOIN?
ON defines which rows match, while WHERE filters rows from the resulting dataset.
Why can WHERE change a LEFT JOIN into an INNER JOIN-like result?
A WHERE condition on the right table can remove rows where the right-side columns are NULL.