Week 5: SQL Joins & Relational Data

1. Primary & Foreign Keys

Week 2 defined a primary key as the column that uniquely identifies each row in a table. A foreign key is the other half of a relationship: a column in one table that holds the primary key value of a row in another table, linking the two together.

customer_id | customer_name | region
------------+---------------+--------
1           | Asha Rao      | North
2           | Ben Diaz      | South
3           | Chen Wei      | North
4           | Dev Patel     | East

order_id | customer_id | amount | order_date
---------+-------------+--------+------------
1        | 1           | 1200   | 2026-01-04
2        | 2           | 450    | 2026-01-05
3        | 1           | 890    | 2026-01-06
4        | 1           | 300    | 2026-01-09
5        | 4           | NULL   | 2026-01-10

Every example this week runs against these two tables. Notice customer_name and region now live in exactly one place — customers — instead of repeating on every order row like Week 2's single flat table did. Notice too that Chen Wei (customer_id 3) has no orders at all — kept deliberately, since it's exactly the case that separates the different join types in this lesson.

2. INNER JOIN

INNER JOIN returns only rows that have a match in both tables — the default, and by far the most common join.

SELECT o.order_id, c.customer_name, c.region, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- Returns 5 rows -- every order, each matched with its customer's name and region.
-- Chen Wei doesn't appear at all: she has no matching row in `orders`.

o and c are table aliases — short names standing in for orders and customers, so o.customer_id and c.customer_id unambiguously say which table each column comes from. The ON clause is the actual matching condition: for every order row, find the customer row whose customer_id matches. Any row on either side with no match — Chen Wei, here — is simply left out of an INNER JOIN's results.

3. LEFT, RIGHT & FULL Joins

The three outer joins all keep rows that have no match on one side, filling the unmatched columns with NULL — useful whenever "which customers have never ordered anything" is itself the actual question.

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Returns 6 rows -- all 5 orders, PLUS one row for Chen Wei with
-- order_id and amount both NULL, since she has no matching order

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Returns just "Chen Wei" -- the WHERE clause keeps only rows where the
-- LEFT JOIN found no match at all

-- RIGHT JOIN: mirror image of LEFT -- every row from the RIGHT table, matched or not
SELECT c.customer_name, o.order_id
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;
-- (identical result to the LEFT JOIN two examples up, just with the tables swapped)

-- FULL JOIN: every row from BOTH tables, matched or not -- rare in practice,
-- but the one to reach for when neither side should ever be silently dropped
SELECT c.customer_name, o.order_id
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id;

RIGHT JOIN is genuinely just a LEFT JOIN with the two tables swapped — most people standardize on always writing LEFT JOIN and reordering the tables instead, purely for consistency. Not every database engine supports FULL JOIN directly (MySQL notably doesn't — it has to be simulated with a UNION of a LEFT and a RIGHT join), which is part of why it's the least common of the four in practice.

4. Combining Joins With WHERE, GROUP BY & Aggregates

A join is just another clause in the same query — everything from Weeks 1 and 2 still works exactly the same way once the tables are joined together:

-- Total spend per customer, North region only, highest first
SELECT c.customer_name, SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.region = 'North'
GROUP BY c.customer_name
ORDER BY total_spent DESC;

-- Asha Rao   2390
-- (Chen Wei doesn't appear -- INNER JOIN already excluded her before GROUP BY ran)

The order these clauses conceptually run in matters: the join happens first (producing one combined table), then WHERE filters individual rows, then GROUP BY collapses what's left, then ORDER BY sorts the final result. Choosing INNER JOIN vs. LEFT JOIN here genuinely changes the answer — an INNER JOIN would never include a zero-order customer in a "total spend" report at all, which is usually exactly right, but worth being a deliberate choice rather than an accident.

5. Fan-Out: Spotting Accidental Row Duplication

A fan-out happens when a join matches one row on one side to multiple rows on the other — every additional match multiplies that row, and a SUM or COUNT computed afterward silently inflates.

-- Asha Rao has 3 orders. Joining customers to orders "fans out" her one
-- customer row into 3 rows -- one per matching order.
SELECT c.customer_name, c.region
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_name = 'Asha Rao';

-- Returns 3 identical-looking rows, NOT 1 -- easy to miss if you're not counting

This becomes a real bug the moment something outside the join gets aggregated alongside it — a customer-level value (like a loyalty-program discount stored once per customer) summed after joining to orders would get counted three times for Asha Rao, once per fanned-out row, wildly overstating the real total.

6. Hands-on Exercise

7. Knowledge Check

Four quick questions. Expand each to check your answer.