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 →