Joins for analysts

⏱ 30 min

Hook

The head of growth asks the question this course opened with: "Which region drove the most revenue last quarter?" You reach for the orders table and hit a wall — it has amounts, but no region. Region lives on the customers table. The number the stakeholder wants does not exist in either table alone; it only appears when you connect the two. Connecting tables is what a join does, and doing it correctly is the difference between a right answer and a confidently wrong one.

Concept

Databases split data across tables on purpose. customers holds who a customer is — name, region, signup date — stored once. orders holds what was bought, and instead of repeating the customer's region on every order, each order carries a customer_id that points back to the customer. That pointer is a foreign key: a column whose value matches the primary key (the unique id) of another table.

A join stitches the tables back together by matching those keys. To answer "revenue by region," you join each order to its customer, borrow the region, then aggregate.

SELECT c.region, SUM(o.amount) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.region;

Read the ON clause as the matching rule: pair each order with the customer whose id it carries. The c and o are table aliases — short nicknames so you can write c.region instead of the full table name, and so the reader knows which table each column comes from when both tables are in play.

The join type decides what happens to rows with no match, and analysts care about this deeply:

Join type	Keeps
INNER JOIN	Only rows that match on both sides
LEFT JOIN	Every row from the left table, matched or not

An INNER JOIN (the default when you write plain JOIN) drops unmatched rows. So a customer who never ordered vanishes from an inner join of customers to orders — which is exactly what you want for "revenue by region," but exactly wrong for "which customers have never ordered." For that, a LEFT JOIN keeps every customer, filling the order columns with NULL (SQL's "no value") where there is no match; you then keep the NULL rows.

Now the trap. A join can produce more rows than the table you started with. If one customer has three orders, joining customers to orders gives three rows for that customer — the customer's details repeated once per order. This is called fan-out, and it is the most common way analyst queries go silently wrong. It is harmless when you are summing order amounts (you want every order). It is a disaster when you then count customers: COUNT(*) counts join rows, not customers, and reports too many. The fix is to count the distinct thing you actually mean: COUNT(DISTINCT c.customer_id).

🧠 Knowledge check

1. You need every customer, including those who have never placed an order. Which join?

a) INNER JOIN customers to ordersb) LEFT JOIN customers to ordersc) GROUP BY customerd) A join cannot keep unmatched rows

2. After joining customers to orders, why does COUNT(*) overstate the number of customers?

a) COUNT(*) ignores NULLsb) The join repeats each customer once per order (fan-out), so rows exceed customersc) COUNT(*) only works on one tabled) GROUP BY removes customers

Worked example

Let me answer the head of growth's question and then show the fan-out trap in action so you recognize it in your own work. Revenue by region, excluding cancelled orders, biggest region first:

SELECT c.region, ROUND(SUM(o.amount), 2) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status <> 'cancelled'
GROUP BY c.region
ORDER BY revenue DESC;

region  revenue
East    390.0
West    360.0
North   125.0

The answer: "East led at $390, just ahead of West at$360; North trails at $125." An inner join was the right call here — a customer with no orders contributes no revenue, so dropping them changes nothing.

Now watch the trap. Suppose the growth lead follows up: "How many customers do we have in each region?" The tempting move is to reuse the join and count:

SELECT c.region, COUNT(*) AS customer_count
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.region;

This is wrong. It counts join rows, so a customer with two orders is counted twice, and any customer with zero orders is missing entirely. The honest query counts distinct customers straight from the customers table:

SELECT region, COUNT(DISTINCT customer_id) AS customer_count
FROM customers
GROUP BY region;

Same verb, "count," but the first version answers "how many orders' worth of customer-rows," and only the second answers the question that was asked. When a count looks too high after a join, fan-out is the first thing to suspect.

Hands-on

Your turn, with the mirror-image join. The exercise below loads customers and orders for the store. The retention manager wants a win-back list: every customer who has never placed an order, with their name and region. That is a LEFT JOIN from customers to orders, keeping only the rows where the order side is NULL. Write it.

▶ SQL Kingdomda101-joins-customers-with-no-orders

Practice in SQL Kingdom ↗

Success criteria: your result lists exactly the customers with no matching orders — no one who has ordered, and everyone who has not. SQL Kingdom checks your result and awards XP when it matches.

Recap

• You can connect tables on a foreign-key match with JOIN ... ON, using aliases to keep columns readable.
• You can choose INNER JOIN to keep only matches, or LEFT JOIN to keep every left-side row and surface the unmatched ones via NULL.
• You can recognize fan-out — a join multiplying rows per key — and count the right thing with COUNT(DISTINCT ...).

Next up: you now have every clause needed to answer a real stakeholder question from raw tables. In the lab, you will do exactly that end to end — answer a business question with your own SQL.

🧠 Knowledge check

1. In `FROM customers AS c JOIN orders AS o ON o.customer_id = c.customer_id`, what does the ON clause specify?

a) Which columns to returnb) The rule for matching a row in one table to a row in the otherc) The sort order of the resultd) Which rows to exclude after grouping

2. A LEFT JOIN of customers to orders shows NULL in the order columns for one customer. What does that mean?

a) The data is corruptb) That customer has no matching orderc) The join failedd) That customer was cancelled

📌 Key takeaways

• Joins reconnect tables on a foreign-key-to-primary-key match with JOIN ... ON.
• INNER JOIN keeps only matches; LEFT JOIN keeps every left row and marks unmatched right columns NULL.
• Fan-out multiplies rows per key — sum safely, but count with COUNT(DISTINCT ...).