LEFT JOIN

Keeps every row of the left table, with NULLs where the right table has no match.

Syntax

SELECT ...
FROM a
LEFT [OUTER] JOIN b ON b.a_id = a.id [AND extra_condition_on_b]

-- anti-join: rows of a without a match in b
FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.a_id IS NULL

A left join returns all inner-join matches plus each left row that matched nothing, padded with NULLs for b's columns. RIGHT JOIN is the mirror image (rarely needed: swap the tables).

Where you put a condition matters. A condition on b in ON filters which b rows can match (left rows stay). The same condition in WHERE runs after the join and removes the NULL-padded rows, turning the left join back into an inner join.
Anti-join: WHERE b.key IS NULL (test a column that is NOT NULL in b, usually its key) finds left rows with no match. NOT EXISTS does the same and is often clearer.
Count matches with count(b.id), not count(*) — count(*) counts the padded row as 1.
Use COALESCE(sum(b.x), 0) to show 0 instead of NULL for unmatched rows.
Fan-out still applies: one left row with three matches becomes three rows.

Examples

All products, with order counts starter

SELECT p.product_name, count(o.order_id) AS orders
FROM products p
LEFT JOIN orders o ON o.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY orders, p.product_name;

Products never ordered appear with 0 because count(o.order_id) skips the NULL padding.

Customers who never ordered (anti-join) shop

SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id
LIMIT 10;

Only left rows that found no order keep a NULL order_id.

Condition in ON vs WHERE starter

SELECT c.customer_name,
       count(o.order_id) AS cancelled_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'cancelled'
GROUP BY c.customer_id, c.customer_name
ORDER BY cancelled_orders DESC, c.customer_name;

Every customer is listed, most with 0. Moving the status test to WHERE would drop customers without a cancelled order.

Pitfalls

Filtering the right table in WHERE (turns the LEFT JOIN into an INNER JOIN).
count(*) instead of count(right_table.col): unmatched rows count as 1.
Anti-join testing a nullable column of b for IS NULL (matches rows that did join but have NULL there).
Chaining LEFT JOIN then INNER JOIN on the right table: the inner join removes the unmatched rows again.