SQL for Product Managers
JOINs: Connecting Tables Without Breaking Your Numbers
A JOIN combines rows from two tables based on a matching key. Getting the join type wrong is the single most common way a PM ships a wrong number with total confidence.
Recall the users and orders tables from Lesson 1.2. To answer "show me each order along with the email of the person who placed it," you need to join them.
SELECT users.email, orders.amount, orders.order_date
FROM orders
JOIN users ON orders.user_id = users.id;
But "JOIN" alone is shorthand for INNER JOIN, and there are several types, and they behave very differently the moment your data isn't perfectly clean, which real data never is.
Join type | What it keeps | When to use it |
|---|---|---|
INNER JOIN | Only rows that match in both tables | You only want users who have placed at least one order |
LEFT JOIN | Every row from the left (first) table, plus matches from the right, filled with NULL where there's no match | You want every user, including ones who've never ordered anything, i.e. exactly the users who should show a count of zero |
RIGHT JOIN | The mirror image of LEFT JOIN | Rare in practice; usually people rewrite as a LEFT JOIN with tables swapped |
FULL JOIN | Every row from both tables, matched where possible | You want a complete picture of two tables even where neither side has a match |
WORKED EXAMPLE, AND WHY THIS MATTERS
You want to calculate "percentage of users who have ever placed an order." You write:
SELECT COUNT(DISTINCT users.id) AS users_with_orders
FROM users
JOIN orders ON users.id = orders.user_id;
This uses an INNER JOIN, which silently drops every user who has never placed an order, because there's no matching row in orders for them to join against. Your count of "users with orders" is accidentally also your count of "all users," if you forgot you needed the denominator too, and any percentage you calculate from this is meaningless. The fix, if you separately need the total user count, is either a second, ungrouped query against users alone, or restructuring the whole thing around a LEFT JOIN so users with zero orders show up with a count of zero instead of disappearing.
This is not a hypothetical. This exact mistake, INNER JOIN silently excluding the people you actually needed to count, is one of the most common causes of a metric that looks plausible, gets presented in a meeting, and is quietly wrong.
YOUR MOVE THIS WEEK
Take any two related tables you have access to. Write the same basic query twice, once with INNER JOIN and once with LEFT JOIN. Compare the row counts. If they're different, you've just seen, with your own eyes, exactly how a join choice can change your answer.