Mastering SQL Joins
INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF joins — reassembling the tables normalization split apart.
Overview
Normalization splits related data across multiple tables. A join is the operation that puts it back together for a read, combining rows from two tables based on a matching column — usually a foreign key pointing at a primary key. The different join types — INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF — don't do different things to the tables; they differ only in what happens to rows on either side that don't have a match.
Why It Exists
This chapter is the direct payoff of the last few: keys and constraints created a real, enforced relationship between `customers` and `orders`, and a join is how you read across that relationship in a single query instead of running one query per table and stitching the results together in application code. Different join types exist because 'what should happen to unmatched rows' is a real, meaningful choice, not a formality — a report that should include customers with zero orders needs a fundamentally different join than one that should only include customers who've actually ordered something.
Real World Example
Joining `customers` to `orders` on `customers.id = orders.customer_id`: an INNER JOIN returns only customers who have at least one order — anyone with zero orders disappears from the result entirely. A LEFT JOIN returns every customer, whether they have orders or not — customers with no orders still appear, with the order columns coming back as null. This distinction causes a genuinely common production bug: someone builds a 'customers' report with an INNER JOIN to pull in order totals, and new customers who haven't ordered yet silently vanish from the report, because INNER JOIN was the wrong choice for a question that was really about all customers.
Example Data
customers
| id | name |
|---|---|
| 1 | Sarah Johnson |
| 2 | Michael Carter |
| 3 | Emma Davis |
orders — Emma Davis (id 3) has no rows here yet
| id | customer_id | product |
|---|---|---|
| 101 | 1 | Laptop |
| 102 | 1 | Mouse |
| 103 | 2 | Keyboard |
INNER JOIN result — Emma Davis silently disappears
| name | product |
|---|---|
| Sarah Johnson | Laptop |
| Sarah Johnson | Mouse |
| Michael Carter | Keyboard |
LEFT JOIN result — every customer still appears, Emma Davis with a null product
| name | product |
|---|---|
| Sarah Johnson | Laptop |
| Sarah Johnson | Mouse |
| Michael Carter | Keyboard |
| Emma Davis | null |
How It Works
INNER JOIN keeps only rows that have a match on both sides. LEFT JOIN keeps every row from the left table, filling in nulls for any unmatched columns from the right table. RIGHT JOIN is the mirror of LEFT — every row from the right table, nulls for unmatched left columns (in practice, most people just flip the table order and use LEFT instead). FULL OUTER JOIN keeps every row from both tables, with nulls on whichever side didn't match. CROSS JOIN has no matching condition at all — it returns every possible combination of rows from both tables, which is rarely what you actually want unless you're deliberately generating combinations. SELF JOIN isn't a different join type mechanically — it's any of the above, applied to a table joined against itself, typically to model a hierarchy, like an `employees` table where each row's `manager_id` points at another row in the same table.
Diagram
Same match condition, different rules for what happens to unmatched rows
INNER JOIN
only rows matching on both sides
LEFT JOIN
all of left, nulls for unmatched right
RIGHT JOIN
all of right, nulls for unmatched left
FULL OUTER JOIN
all rows from both sides, nulls where unmatched
CROSS JOIN
every combination — no match condition
SELF JOIN
a table joined to itself, e.g. employee → manager
Common Mistakes
Using INNER JOIN when LEFT JOIN was actually needed
Why: It silently drops every row on the left side that has no match — like customers with zero orders disappearing entirely from a customer report.
Fix: Before writing the join, ask explicitly: should rows with no match on the other side still appear? If yes, that's LEFT (or FULL OUTER), not INNER.
Writing an old-style comma join with no explicit ON condition, or forgetting the ON clause entirely
Why: Without a match condition, the result is effectively a CROSS JOIN — every row from one table combined with every row from the other, which explodes the row count and is almost never the intended result.
Fix: Always write an explicit JOIN ... ON condition, and treat CROSS JOIN as something you write deliberately, not something you fall into by accident.
Self-joining a table without clearly aliasing both instances
Why: Without distinct aliases, it's ambiguous — and often impossible — to reference 'the other row' the join is comparing against.
Fix: Always alias both sides of a self-join clearly, like `employees e JOIN employees m ON e.manager_id = m.id`.
Interview Questions
What's the difference between an INNER JOIN and a LEFT JOIN?
INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table regardless of whether it has a match, filling in null for any columns from the right table when there isn't one.
When would you use a CROSS JOIN on purpose?
When you genuinely need every combination of two sets of rows — for example, generating every possible pairing of available dates and available time slots to build a schedule grid. Outside of deliberately generating combinations like that, an unintended CROSS JOIN (often from a missing ON condition) is a bug, not a feature.
How would you model and query a multi-level management hierarchy stored in a single employees table?
Each row stores a `manager_id` referencing another row in the same table (a self-referencing foreign key). A single self-join with clear aliases answers one level at a time — employees joined to their direct manager. For an arbitrary number of levels, most engines support a recursive common table expression (`WITH RECURSIVE`), which repeatedly joins the table to itself, following `manager_id` up the chain until it reaches a row with no manager.
Production Best Practices
Do
✓Decide explicitly whether unmatched rows should appear before picking a join type.
✓Always write an explicit ON condition for every join.
✓Alias both sides clearly whenever you self-join a table.
Don't
✗Don't default to INNER JOIN without checking whether it silently excludes rows you need.
✗Don't write a comma-separated join with no ON condition — it's an accidental CROSS JOIN.
✗Don't leave a self-join's two table instances unaliased or ambiguously named.
Comparison
| Unmatched Left Rows | Unmatched Right Rows | |
|---|---|---|
| INNER JOIN | Dropped | Dropped |
| LEFT JOIN | Kept (nulls on right) | Dropped |
| RIGHT JOIN | Dropped | Kept (nulls on left) |
| FULL OUTER JOIN | Kept (nulls on right) | Kept (nulls on left) |
| CROSS JOIN | N/A — no match condition | N/A — no match condition |