Essential storage keeps sign-in and learning features working. Google Analytics measures visits; Microsoft Clarity records interactions to help us improve usability. Both are optional and stay off until you choose. Read our privacy policy.
Preserve the left-side population, treat NULL as no match, and avoid a WHERE that silently turns the outer join inner.
This lesson continues the SQL JOIN overview. INNER JOIN drops customers with no order. LEFT JOIN is the tool when the question starts with the left table: “show every customer, and any orders they have.”
PreserveKeep every left-side row, match or not.
GrainMatching children can still repeat a parent.
NULLA missing match is unknown, not zero.
ON vs WHEREA late filter can erase the unmatched rows you meant to keep.
LEFT JOIN keeps the complete left-side population
A LEFT JOIN retains every row from the table on its left. When no order matches, the order columns are NULL. Find Celia and Elias: they have no order ID because no partner row exists, not because the database invented a zero. The unmatched import order 105 does not appear, because this report starts from customers.
SQL BROWSER RUNNER
Keep customers without orders
Preserve every customer while adding order details where a match exists.
CREATETABLE customers (
customer_id INTEGERPRIMARYKEY,
customer_name TEXTNOTNULL,
city TEXTNOTNULL);CREATETABLE orders (
order_id INTEGERPRIMARYKEY,
customer_id INTEGER,statusTEXTNOTNULL,
amount_cents INTEGERNOTNULL);INSERTINTO customers (customer_id, customer_name, city)VALUES(1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat'),(5,'Elias','Tangier');INSERTINTO orders (order_id, customer_id,status, amount_cents)VALUES(101,1,'paid',3500),(102,1,'pending',800),(103,2,'paid',2200),(104,4,'paid',6000),(105,99,'paid',900);-- deliberately unmatched: an import problem to auditSELECT c.customer_name, c.city, o.order_id, o.statusFROM customers AS c
LEFTJOIN orders AS o ON o.customer_id = c.customer_id
ORDERBY c.customer_id, o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.
Matching children can still multiply left rows
Preservation is not the same as one row per customer. Amina has two orders, so she produces two joined rows. The result grain is customer-order pair. If you later SUM(o.amount_cents) without grouping, you are summing at pair grain. That may be correct for a line-level list and wrong for a per-customer dashboard.
A filter on the nullable side belongs in ON when unmatched rows matter
This is one of the most important outer-join decisions. Put o.status = 'paid' in ON when the question is “show every customer and their paid orders, if any.” That limits matching orders but still preserves every customer. In contrast, a WHERE o.status = 'paid' runs after the join and removes the NULL order rows—silently defeating the reason for the left join.
SQL BROWSER RUNNER
Preserve every customer with paid-order matches
Put the paid-order condition in ON so zero-paid-order customers remain visible.
CREATETABLE customers (
customer_id INTEGERPRIMARYKEY,
customer_name TEXTNOTNULL,
city TEXTNOTNULL);CREATETABLE orders (
order_id INTEGERPRIMARYKEY,
customer_id INTEGER,statusTEXTNOTNULL,
amount_cents INTEGERNOTNULL);INSERTINTO customers (customer_id, customer_name, city)VALUES(1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat'),(5,'Elias','Tangier');INSERTINTO orders (order_id, customer_id,status, amount_cents)VALUES(101,1,'paid',3500),(102,1,'pending',800),(103,2,'paid',2200),(104,4,'paid',6000),(105,99,'paid',900);-- deliberately unmatched: an import problem to auditSELECT c.customer_name, o.order_id, o.amount_cents
FROM customers AS c
LEFTJOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status='paid'ORDERBY c.customer_id, o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.
SQL BROWSER RUNNER
See how WHERE removes unmatched customers
Run the superficially similar query with the same filter after the join.
CREATETABLE customers (
customer_id INTEGERPRIMARYKEY,
customer_name TEXTNOTNULL,
city TEXTNOTNULL);CREATETABLE orders (
order_id INTEGERPRIMARYKEY,
customer_id INTEGER,statusTEXTNOTNULL,
amount_cents INTEGERNOTNULL);INSERTINTO customers (customer_id, customer_name, city)VALUES(1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat'),(5,'Elias','Tangier');INSERTINTO orders (order_id, customer_id,status, amount_cents)VALUES(101,1,'paid',3500),(102,1,'pending',800),(103,2,'paid',2200),(104,4,'paid',6000),(105,99,'paid',900);-- deliberately unmatched: an import problem to auditSELECT c.customer_name, o.order_id, o.amount_cents
FROM customers AS c
LEFTJOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status='paid'ORDERBY c.customer_id, o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.
Common LEFT JOIN mistakes
WHERE on nullable side
Outer rows vanish
Place a match-only filter in ON when the report must keep rows with no match.
COUNT(*)
Zeros become ones
COUNT(o.order_id) ignores the NULL placeholder. COUNT(*) counts the preserved customer row too.
No result grain
Inflated totals
LEFT JOIN still repeats a parent for each matching child. Inspect pairs before you aggregate.
NULL as zero
Missing match misread
NULL means no partner row. Replace it for display only after you understand that absence.
Independent lab: customer paid-order totals
Build a report with every customer's count and total of paid orders. Amina should have one paid order worth 3,500 cents; Dina should have one worth 6,000; Celia and Elias should remain with zero. The unmatched import order must not appear because this report starts from the customer population. Use COUNT(o.order_id), not COUNT(*), and turn a missing sum into zero with COALESCE. In a real dashboard, this pattern answers “who has not purchased yet?” as reliably as it answers “who has purchased?”
SQL BROWSER RUNNER
Audit a customer paid-order report
Preserve every customer, then count and total only their paid orders.
CREATETABLE customers (
customer_id INTEGERPRIMARYKEY,
customer_name TEXTNOTNULL,
city TEXTNOTNULL);CREATETABLE orders (
order_id INTEGERPRIMARYKEY,
customer_id INTEGER,statusTEXTNOTNULL,
amount_cents INTEGERNOTNULL);INSERTINTO customers (customer_id, customer_name, city)VALUES(1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat'),(5,'Elias','Tangier');INSERTINTO orders (order_id, customer_id,status, amount_cents)VALUES(101,1,'paid',3500),(102,1,'pending',800),(103,2,'paid',2200),(104,4,'paid',6000),(105,99,'paid',900);-- deliberately unmatched: an import problem to audit-- Keep every customer, then summarize only their paid orders.SELECT c.customer_id, c.customer_name,COUNT(o.order_id)AS paid_order_count,COALESCE(SUM(o.amount_cents),0)AS paid_cents
FROM customers AS c
LEFTJOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status='paid'GROUPBY c.customer_id, c.customer_name
ORDERBY paid_cents DESC, c.customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.
Lesson review
LEFT JOIN is a promise to keep the left-side population. NULL on the right is a missing match. Matching children can still multiply rows, so grain still matters. A right-side filter in WHERE can erase the unmatched rows you meant to keep; put that filter in ON when zero activity is part of the answer.
I can predict which rows a LEFT JOIN keeps.
I can read NULL as a missing match, not a zero.
I can explain why grain can still multiply left rows.
I can preserve unmatched rows while matching only a filtered right-side subset.
I can write a zero-activity report without erasing its zero-activity rows.
KNOWLEDGE CHECK
Check LEFT JOIN reasoning
Answer all eight questions, then revisit the ON versus WHERE runners if unmatched customers disappeared.
