A junior data analyst runs the following query against a retail sales table and receives 42,000 rows instead of the expected 12,000: SELECT c.customer_id, c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_status <> 'cancelled'; Which action should the analyst take to return all customers, including those with no orders, while still excluding cancelled orders?
Placing the filter in the ON clause preserves all rows from the left table, so customers with no orders still appear with NULL order values, while cancelled orders are excluded from the join matches. The result set includes every customer and only non-cancelled orders, which matches the stated requirement exactly. Filtering in the WHERE clause instead converts the outer join into an inner join.
Why this answer
In a LEFT JOIN, predicates placed in the WHERE clause are evaluated after the join and remove rows where the right-side column is NULL, effectively turning the outer join into an inner join. Moving the status filter into the ON clause restricts which orders are matched without discarding customers who have no orders. This is the standard fix for returning all customers while excluding cancelled orders.
Exam trap
The trap here is assuming that a WHERE clause filter on the right table of a LEFT JOIN behaves the same as a filter in the ON clause, when it actually eliminates the unmatched left rows.