Cheat sheets · Module 3
Joining Tables cheat sheet
Answer questions that span customers, orders and more.
INNER JOIN
JOIN combines rows from two tables wherever the ON condition matches, usually a foreign key such as orders.customer_id = customers.id. Give tables short aliases (o, c) and prefix columns with them.
SELECT i.id, p.title FROM order_items i JOIN products p ON p.id = i.product_id;Practice: Orders with customer names →
INNER JOIN vs LEFT JOIN
INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left (first) table; when there's no match, the right table's columns come back as NULL.
SELECT a.name, b.title FROM authors a LEFT JOIN books b ON b.author_id = a.id;Practice: Every user and their plan →
LEFT JOIN · IS NULL
To find rows with no match (an 'anti-join'), LEFT JOIN and keep the rows where the right side came back empty: WHERE right.id IS NULL. NULL never equals anything, so = NULL is always false.
SELECT a.name FROM authors a LEFT JOIN books b ON b.author_id = a.id WHERE b.id IS NULL;Practice: Users who never subscribed →
JOIN + GROUP BY
You can aggregate across a join: join first so every row has the columns you need, then GROUP BY a column from either table.
SELECT a.country, COUNT(*) AS books FROM books b JOIN authors a ON a.id = b.author_id GROUP BY a.country;Practice: Revenue by country →
Three-table JOIN
You can chain JOINs: each one adds a table and its own ON condition. Join every table on the column that really links them (a foreign key like user_id points to users.id), not on matching id columns that just happen to look alike.
SELECT s.name, c.title, e.grade FROM students s JOIN enrollments e ON e.student_id = s.id JOIN courses c ON c.id = e.course_id;Practice: Plan behind each purchase →
LEFT JOIN · COUNT(col) vs COUNT(*)
To count related rows and still show zeros, LEFT JOIN and count a column from the right table. COUNT(*) counts rows, and a LEFT JOIN gives a customer with no orders one row full of NULLs, so COUNT(*) says 1. COUNT(o.id) skips NULLs and says 0.
SELECT a.name, COUNT(b.id) AS books FROM authors a LEFT JOIN books b ON b.author_id = a.id GROUP BY a.name;Practice: Order count, including zero →
LEFT JOIN · filter in ON vs WHERE
With a LEFT JOIN, where you filter the right table matters. A condition in ON decides which rows match, and unmatched left rows still survive with NULLs. The same condition in WHERE runs after the join, and NULL fails it, so those rows vanish and you're back to an inner join.
SELECT a.name, b.title AS bestseller FROM authors a LEFT JOIN books b ON b.author_id = a.id AND b.is_bestseller = true;Practice: Purchase date for every user →
IN (subquery) · EXISTS
To filter rows by whether a match exists in another table, use WHERE id IN (SELECT ...) or WHERE EXISTS (SELECT 1 ... WHERE ...). Unlike a JOIN, these never duplicate rows: a customer with three matching orders still comes back once.
SELECT name FROM authors WHERE id IN ( SELECT author_id FROM books WHERE rating >= 4.5 );Practice: Customers with a big order →
UNION vs UNION ALL
UNION stacks the rows of two queries with matching columns into one result, then removes duplicate rows. UNION ALL stacks them and keeps every row. Use UNION ALL whenever the repeats are real data, for example when you're about to count them.
SELECT customer_id, COUNT(*) AS contacts FROM ( SELECT customer_id FROM calls UNION ALL SELECT customer_id FROM emails ) AS c GROUP BY customer_id;Practice: Total touchpoints per user →