The WHERE clause that quietly changes your join type

Question

Accepted Answer

LEFT JOIN produces NULL in all right-table columns for unmatched left rows. Any WHERE condition that requires a right-table column to be non-NULL filters those rows out - effectively turning the LEFT JOIN into an INNER JOIN. WHERE customers.name IS NOT NULL removes every row where no customer was found, which are exactly the unmatched rows LEFT JOIN was supposed to keep. The fix: move the condition into the ON clause, or use WHERE customers.name IS NULL / IS NOT NULL intentionally for 'find unmatched' queries.