Keep the rows an INNER JOIN would drop

An INNER JOIN silently drops any row without a match. That's often exactly wrong. If you're building "orders per customer," a customer with zero orders should show 0, not vanish. Dropping them understates your customer base and hides the very thing you might be investigating. The fix is LEFT JOIN: keep every row from the left (driving) table, and fill the right table's columns with NULL where there's no match.

Worked example, every customer, with their order count, including the silent ones:

SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

A customer with no orders still appears; their o.order_id is NULL, and COUNT(o.order_id) (which skips NULLs) correctly returns 0 for them.

customers · LEFT JOIN · orders ON customer_id = customer_id

Step 1 / 3

customers

customer_id	customer_name
1	Ada
2	Grace
3	Alan

orders

order_id	customer_id
100	matched: 1
101	matched: 1
102	2

customer_id=1 matches 2 orders rows.

Result (2 rows)

customers.customer_id	customers.customer_name	orders.order_id	orders.customer_id
1	Ada	100	1
1	Ada	101	1

LEFT JOIN keeps every customer. Alan (customer 3) has no order, so his orders columns are NULL-padded instead of dropped.

Anatomy (the NULL is the whole point):

customers (LEFT)   LEFT JOIN   orders (RIGHT)
  every row kept  ───────────►  matched cols filled, else NULL

Two rules that make left joins behave:

COUNT(right.col) not COUNT(*) in an aggregate. COUNT(*) counts the NULL-padded row as 1, giving a customer with no orders a count of 1 instead of 0. COUNT(o.order_id) skips the NULL and returns 0. This is the single most common left-join-plus-aggregate bug.
Filtering the right table in WHERE secretly turns a LEFT JOIN into an INNER JOIN. A condition like WHERE o.status = 'paid' rejects the NULL-padded no-match rows (because NULL = 'paid' is not true), silently dropping the very rows you left-joined to preserve. If you must filter the right side, put the condition in the ON clause (LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid') so unmatched left rows survive.

Keep it readable / common pitfall: use COALESCE(right.col, default) to turn the NULLs into a sensible display value: COALESCE(SUM(o.total_cents), 0) shows 0 revenue for a customer who never bought, instead of a blank.

Recap: LEFT JOIN preserves every driving-table row and NULL-pads missing matches; aggregate with COUNT(right.col) for a true 0, and never filter the right table in WHERE or you collapse it back to an inner join.

CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, customer_name TEXT ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER ); INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Alan'); -- Alan has never ordered INSERT INTO orders VALUES (100, 1), (101, 1), (102, 2);

Make it stick

A second problem on the same idea, plus 3 bonus drills.

Product coverage report: For every product (including ones that never sold), report total units sold. Return product_id, product_name, and units_sold (sum of quantity from matching order_items, shown as 0, not NULL, when the product never sold), sorted by product_id. A product with no sales must appear with units_sold = 0.

4 hints and 1 automated check are waiting in the workspace.

LEFT JOIN and Preserving Rows

Keep the rows an INNER JOIN would drop

Your turn

Make it stick