LEFT JOIN
Preserve every row on the left and match records on the right when they exist — and avoid the WHERE clause that quietly turns it back into an INNER JOIN.
Syntax
SELECT ...
FROM left_table lt
LEFT JOIN right_table rt
ON lt.key = rt.key;(LEFT OUTER JOIN is the same thing — OUTER is optional.)
What it does
Keeps every row from the left table, matched or not. Right-side columns are
NULL where there's no match.
Loading editor…
Customers 6 and 7 show up with NULL order columns — unlike
INNER JOIN, they aren't dropped.
The classic bug: a WHERE filter collapses it back to INNER
Loading editor…
Customers 6 and 7 vanished. o.status = 'paid' runs in WHERE after the
join, and NULL = 'paid' is UNKNOWN — so every unmatched row gets filtered
out, and the LEFT JOIN behaves exactly like an INNER JOIN. See
WHERE: LEFT JOIN filters belong in ON
for why.
Move the condition into ON to keep the unmatched rows:
Loading editor…
Same filter, same join — but now it's part of which rows match, not which
rows survive, so customers 6 and 7 stay with NULL order columns.
Hit this as an actual error?
- WHERE — the full explanation of NULL-in-WHERE and the ON-vs-WHERE distinction.
- Rows missing in another table — LEFT JOIN … IS NULL as an anti-join.
- COALESCE — filling those NULL right-side columns for display.
- APPLY — T-SQL's version of this, for when the right side needs a TOP N or a function call per row.
Safety check before shipping
- confirm whether unmatched rows are expected in the output
- decide whether right-side
NULLs should be coalesced or left explicit - make sure any condition on the right table is in
ON, notWHERE, unless collapsing to an inner join is the actual intent