Learning Goals
3 min- Tell when to use INNER vs LEFT JOIN.
- Read NULL columns in the result and handle them with
COALESCE/IFNULL. - Use LEFT JOIN to find "rows with no match" —
WHERE right.id IS NULL. - Aggregate left-joined data correctly (zeros, not missing rows).
Warm-Up · See What INNER Drops
5 minYesterday's query, run again:
SELECT c.name, p.name FROM classes c INNER JOIN pupils p ON p.class_id = c.class_id;
Notice: Form 1C never appears, because no pupil has class_id = 3. INNER JOIN silently drops it.
-- swap INNER for LEFT SELECT c.name AS class, p.name AS pupil FROM classes c LEFT JOIN pupils p ON p.class_id = c.class_id;
class pupil Form 1A Aisyah Form 1A Wei Jie Form 1A Anita Form 1B Suresh Form 1B Mei Form 1B Razif Form 1C NULL ← here it is!
Put the table you want to keep on the LEFT. LEFT JOIN keeps every row from that table; columns from the RIGHT come in as NULL when there's no match.
New Concept · LEFT JOIN + NULL
14 minFind the orphans
A common pattern: which rows on the left have NO match on the right?
-- classes with zero pupils SELECT c.name FROM classes c LEFT JOIN pupils p ON p.class_id = c.class_id WHERE p.id IS NULL; -- → Form 1C
The trick: a successful match makes p.id non-null; a missing match leaves it NULL.
COALESCE / IFNULL — present NULL nicely
NULLs make reports ugly. Replace them at display time:
SELECT c.name AS class, COALESCE(p.name, '(no pupils)') AS pupil FROM classes c LEFT JOIN pupils p ON p.class_id = c.class_id;
Aggregate left-joined data
If you naively COUNT(*) after a LEFT JOIN, you can count the row with all-NULL right side. Use COUNT(column from the right table):
SELECT c.name AS class, COUNT(p.id) AS pupils, COALESCE(AVG(p.score), 0) AS avg_score FROM classes c LEFT JOIN pupils p ON p.class_id = c.class_id GROUP BY c.class_id;
class pupils avg_score Form 1A 3 86.0 Form 1B 3 75.7 Form 1C 0 0.0 ← row preserved, count = 0
RIGHT JOIN? FULL JOIN?
Both exist in standard SQL. SQLite supports them as of 3.39 (2022). LEFT JOIN handles 95% of real cases — just swap the order of tables and you've emulated RIGHT JOIN.
Worked Example · Inventory Vs Sales
12 minCREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, stock INTEGER); CREATE TABLE sales (id INTEGER PRIMARY KEY, product_id INTEGER, qty INTEGER); INSERT INTO products VALUES (1,'Roti',100),(2,'Milo',80),(3,'Nasi',50),(4,'Teh',60); INSERT INTO sales VALUES (1,1,3),(2,1,5),(3,2,2),(4,3,1); -- Teh has stock but no sales
-- Products with their total sales (zeros for unsold) SELECT p.name, p.stock, COALESCE(SUM(s.qty), 0) AS sold FROM products p LEFT JOIN sales s ON s.product_id = p.id GROUP BY p.id ORDER BY sold DESC;
name stock sold Roti 100 8 Milo 80 2 Nasi 50 1 Teh 60 0 ← preserved
-- Products with NO sales (good for clearance lists) SELECT p.name FROM products p LEFT JOIN sales s ON s.product_id = p.id WHERE s.id IS NULL;
name Teh
Read the diff
Same data, two business questions: "how is each product doing?" (LEFT JOIN + GROUP BY) and "what should we put on clearance?" (LEFT JOIN + IS NULL). Both impossible with INNER JOIN.
Try It Yourself
13 minReturn every class with its pupil count (including 0).
Hint
SELECT c.name, COUNT(p.id) AS pupils FROM classes c LEFT JOIN pupils p ON p.class_id = c.class_id GROUP BY c.class_id;
Using the homework schema from yesterday (authors + books), find authors with zero books.
Hint
SELECT a.name FROM authors a LEFT JOIN books b ON b.author_id = a.id WHERE b.id IS NULL;
Three-table LEFT JOIN: every book, with the latest loan or "-" if never loaned.
Hint
SELECT b.title, COALESCE(MAX(l.due_date), '—') AS last_loan FROM books b LEFT JOIN loans l ON l.book_id = b.id GROUP BY b.id ORDER BY last_loan ASC;
Mini-Challenge · Customer Loyalty Tier
8 minYou have customers(id, name) and orders(id, customer_id, total). Some customers have never ordered. Write a query that returns every customer with their total spend (0 if none) and a tier: "gold" if spend ≥ 500, "silver" if 100–499, "bronze" if 1–99, "new" if 0.
Show one possible solution
SELECT c.name, COALESCE(SUM(o.total), 0) AS spend, CASE WHEN COALESCE(SUM(o.total), 0) >= 500 THEN 'gold' WHEN COALESCE(SUM(o.total), 0) >= 100 THEN 'silver' WHEN COALESCE(SUM(o.total), 0) > 0 THEN 'bronze' ELSE 'new' END AS tier FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id ORDER BY spend DESC;
Non-negotiables: LEFT JOIN so "new" customers appear, COALESCE for null sums, CASE expression for tiers.
Recap
3 minLEFT JOIN keeps every left row. NULLs appear on the right for unmatched rows. Use COALESCE/IFNULL to render them nicely. WHERE right.id IS NULL is the canonical "find orphans" pattern. With these two lessons (INNER + LEFT) you can answer almost any multi-table question.
Vocabulary Card
- LEFT JOIN
- All rows from the left table; matched columns from the right or NULL.
- NULL
- SQL's "no value". Compared with
IS NULL/IS NOT NULL, never= NULL. - COALESCE / IFNULL
- Return the first non-NULL value. Great for default displays.
- orphan
- A row in one table with no matching row in the related table.
Homework
4 minTake any two-table schema you've built this week. Add one row to the left table with no match on the right, and one row to the right table with no match on the left. Now write five queries:
- Default INNER JOIN — count rows.
- LEFT JOIN — count rows.
- The orphan-finder: left rows with no right match.
- LEFT JOIN + aggregate, with zero defaults for unmatched.
- A "flip-the-direction" LEFT JOIN that finds right-side orphans.
-- INNER count SELECT COUNT(*) FROM classes c INNER JOIN pupils p ON p.class_id = c.class_id; -- LEFT count SELECT COUNT(*) FROM classes c LEFT JOIN pupils p ON p.class_id = c.class_id; -- classes with no pupils SELECT c.name FROM classes c LEFT JOIN pupils p ON p.class_id = c.class_id WHERE p.id IS NULL; -- pupil counts, zero where missing SELECT c.name, COUNT(p.id) FROM classes c LEFT JOIN pupils p ON p.class_id = c.class_id GROUP BY c.class_id; -- flip: pupils with bad class_id SELECT p.name FROM pupils p LEFT JOIN classes c ON p.class_id = c.class_id WHERE c.class_id IS NULL;