Join three tables of Romashka's model and predict the result: find rows without a match with LEFT JOIN … IS NULL and avoid row multiplication in a one-to-many join.
Joining without losing your head
Three tables, “orphans” and row multiplication
A quick refresher if you skipped the “Data Analytics from Scratch” course. INNER JOIN keeps only rows that found a match. LEFT JOIN keeps every row of the left table (the one written before LEFT JOIN), and in rows without a match the fields of the right table are NULL.
Why a systems analyst needs SQL: to check the model and requirements on real data without waiting for a developer.
Three tables. Each JOIN has its own ON condition on a “foreign key → primary key” pair:
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN couriers k ON k.courier_id = o.courier_id
Couriers are joined with LEFT: new orders have no courier yet, and INNER JOIN would silently drop them.
An anti-join finds “orphans” and gaps — rows of the left table without a match:
SELECT o.order_id FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL
The result: orders without a single payment record. NULL is checked only with IS NULL: “= NULL” is never true, not even for NULL.
Row multiplication. Join orders (one row per order) with order_items (N items), and the order row repeats N times. SUM(o.total_amount) overstates revenue, and COUNT(*) counts items, not orders. The fix: count orders with COUNT(DISTINCT o.order_id) and sum order amounts before joining the items.
COUNT(*) counts all rows; COUNT(field) only rows where the field is not NULL.
JOIN traps
| Trap | Symptom | How to do it right |
|---|---|---|
| INNER instead of LEFT | orders without a courier are gone | LEFT JOIN |
| = NULL | zero rows | IS NULL |
| SUM after a 1:N join | the total is larger than the real one | sum before the JOIN |
| COUNT(*) after a 1:N join | items are counted, not orders | COUNT(DISTINCT o.order_id) |
Lesson notes
Three tables, “orphans” and row multiplication
A quick refresher if you skipped the “Data Analytics from Scratch” course. INNER JOIN keeps only rows that found a match. LEFT JOIN keeps every row of the left table (the one written before LEFT JOIN), and in rows without a match the fields of the right table are NULL.
Why a systems analyst needs SQL: to check the model and requirements on real data without waiting for a developer.
Three tables. Each JOIN has its own ON condition on a “foreign key → primary key” pair:
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN couriers k ON k.courier_id = o.courier_id
Couriers are joined with LEFT: new orders have no courier yet, and INNER JOIN would silently drop them.
An anti-join finds “orphans” and gaps — rows of the left table without a match:
SELECT o.order_id FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL
The result: orders without a single payment record. NULL is checked only with IS NULL: “= NULL” is never true, not even for NULL.
Row multiplication. Join orders (one row per order) with order_items (N items), and the order row repeats N times. SUM(o.total_amount) overstates revenue, and COUNT(*) counts items, not orders. The fix: count orders with COUNT(DISTINCT o.order_id) and sum order amounts before joining the items.
COUNT(*) counts all rows; COUNT(field) only rows where the field is not NULL.