Joins are the part of SQL that most people can recite and fewest can debug. The recital is easy. The debugging comes from understanding one thing: a join decides which rows survive, and every wrong answer traces back to rows you did not expect to keep or lose.
The four types, in one table
Picture two tables, orders and customers. Some orders have no matching customer record, and some customers have never ordered. What happens to those rows is the entire difference between the four joins.
| Join | Which rows survive |
|---|---|
| INNER JOIN | Only rows with a match on both sides. The default choice, and the one that silently drops data if you did not want that. |
| LEFT JOIN | Every row from the left table, matched or not. Unmatched right-hand columns come back as NULL. |
| RIGHT JOIN | The mirror image. Rare in practice, because you can swap the table order and use a LEFT JOIN instead. |
| FULL OUTER JOIN | Every row from both sides. Useful for reconciliation, when you need to see what is missing from each. |
How to choose in one question
Ask which table's row count you want the answer to have. If the question is "revenue per customer, including customers who bought nothing", the customer list must survive intact, so it goes on the left of a LEFT JOIN. If the question is "revenue per customer" and non-buyers are irrelevant, an INNER JOIN is correct and cheaper.
SELECT c.name,
COALESCE(SUM(o.amount), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY revenue DESC;The COALESCE matters. Without it, a customer with no orders returns NULL rather than zero, and NULL sorts and sums differently from a number.
The three mistakes
- Filtering the outer table in WHERE. Adding WHERE o.status = 'paid' to a LEFT JOIN throws away the unmatched rows you just went out of your way to keep, because NULL fails the test. Put that condition in the ON clause instead.
- Joining on a non-unique key. If the right-hand table has two rows per key, every left row is duplicated. Your row count grows and every sum doubles.
- Joining on columns of different types. Some databases will coerce, some will error, and some will quietly match nothing at all.
Run the left table's row count on its own, then the join's. For a LEFT JOIN on a unique key they should match. If the join is larger, your key is not unique.
Self joins and why they are not special
A self join is an ordinary join where both sides happen to be the same table, which is how you answer questions about a row's relationship to another row: an employee and their manager, or this month against last month. The only requirement is that you alias each side so the database knows which one you mean.
SELECT e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;Key takeaways
- Pick the join by asking which table's rows must survive.
- Conditions on the outer table belong in ON, not WHERE.
- Compare row counts before and after every join.
- Wrap aggregates over an outer join in COALESCE so gaps come back as zero.
Turn this into a career, not a bookmark.
Data Science & Analytics with AI takes you from your first query to a portfolio employers read.
Explore the course