Problem Statement
Explain when to put predicates in ON versus WHERE for outer joins. Give a rule of thumb.
Explanation
Use ON to define matching logic and any right-side filters you want applied before deciding whether a row matches. This keeps NULL-extended rows when the right side fails either the join or its filter.
Use WHERE for predicates that should apply after the join, typically on the preserved table. A simple rule: put right-side filters in ON when using a LEFT JOIN; put left-side filters in ON when using a RIGHT JOIN.
Code Solution
SolutionRead Only
SELECT c.id, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status='PAID' WHERE c.region='APAC';
