Caso Abierto Español

Field manual · SQL traps

A WHERE on the right-hand table turns your LEFT JOIN back into an INNER JOIN

ON says how rows are glued together; WHERE decides which survive. With INNER JOIN the order doesn't matter; with LEFT JOIN, filtering in the WHERE on a column from the added table strips out exactly the unmatched rows the LEFT JOIN was there to keep.

Where it shows up

Any time a LEFT JOIN is followed by a WHERE that mentions a column from the joined-in table. It's easy to write and the engine never warns you — it just quietly returns fewer rows than you asked for.

Example

Wrong: the WHERE drops orders with no shipment

SELECT o.id, s.carrier
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.id
WHERE s.carrier = 'DHL';
-- orders with no shipment have s.carrier = NULL,
-- and NULL = 'DHL' is NULL: the row is dropped as if this were an INNER JOIN

Right: the condition lives in the ON, not the WHERE

SELECT o.id, s.carrier
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.id
                     AND s.carrier = 'DHL';
-- or, if you genuinely want to exclude the rest:
WHERE s.carrier = 'DHL' OR s.carrier IS NULL

Any comparison with NULL evaluates to NULL, and a row whose WHERE evaluates to NULL isn't returned — that's not an edge case, it's the rule. When the condition is part of the matching criterion ("only DHL shipments, but still keep orders with no shipment"), it belongs in the ON. When you genuinely want to exclude rows from the final result, then it does go in the WHERE — with an added OR column IS NULL so you don't lose the ones that had no match to begin with.