A LEFT JOIN keeps unmatched left-side rows, but placing conditions on the right table in WHERE can remove those unmatched rows and make the result behave like an INNER JOIN.
Understanding filter placement is essential for getting the intended result from OUTER JOIN queries.
Return all customers and only their orders above ₹10,000 while keeping customers without qualifying orders.
SELECT
c.CustomerName,
o.OrderID,
o.OrderAmount
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
AND o.OrderAmount > 10000;This pattern is important in customer analysis, financial reporting, and data-quality checks.
- LEFT JOIN preserves left-side rows.
- Filter placement matters.
- A right-table condition in ON can preserve unmatched left rows.
- A right-table condition in WHERE can remove NULL matches.
- Always test OUTER JOIN filters carefully.
Why can a WHERE condition change a LEFT JOIN result?
A WHERE condition on the right table can remove rows where the right-side value is NULL, effectively eliminating unmatched records.
Where can a right-table filter be placed to preserve unmatched rows?
For a LEFT JOIN, placing the condition in the ON clause can preserve unmatched left-side rows.