The 4 types of SQL join
Inner, left, right, full — which rows survive when two tables meet.
A join combines rows from two tables on a matching condition. INNER keeps only rows that match in both. LEFT keeps all left rows, padding unmatched right columns with NULLs; RIGHT is the mirror. FULL OUTER keeps every row from both sides, NULL-filling wherever there's no match.
Two tables, one condition
A join pairs rows from a left and a right table wherever the ON condition holds — typically a foreign key on one side equalling a primary key on the other. Conceptually the engine considers every possible pairing of rows and keeps those that satisfy the condition. What distinguishes the four join types is purely how they treat the *unmatched* rows that fall outside that condition.
INNER JOIN
An INNER JOIN keeps only rows that find a match on both sides — the intersection of the two tables. Any left row with no matching right row, and any right row with no matching left row, is silently dropped from the result. This is the default and most common join, and it never introduces NULLs of its own.
LEFT JOIN
A LEFT JOIN (LEFT OUTER JOIN) keeps every row from the left table no matter what. Where a matching right row exists, its columns are attached as usual; where none exists, the right-hand columns are filled with NULL. This is how you answer questions like "all customers, including those who have never placed an order."
RIGHT JOIN
A RIGHT JOIN is the exact mirror of a LEFT JOIN: every row from the right table survives, with matched left rows attached and NULLs wherever the left side has no match. It's logically redundant — you can always swap the table order and use a LEFT JOIN instead — which is why teams often standardise on LEFT for readability.
FULL OUTER JOIN
A FULL OUTER JOIN is the union of both sides: every row from both tables appears at least once. Matched rows are combined normally, while unmatched rows from either side are NULL-filled on the columns of the table that had no match. It's the tool for reconciliation questions — finding records that exist in one system but not the other.
Why joins exist: normalization split the data apart
Relational databases deliberately spread related facts across multiple tables. A users table holds one row per person; an orders table holds one row per order with a user_id column pointing back to its owner. This split — the heart of normalization — avoids storing a customer's name and address once per order, which would drift out of sync and waste space.
But the split creates an obvious need: to produce a report of "each order with the customer's name," you must stitch the tables back together at read time. That stitching is the join. A join is the operation that takes two tables and a matching condition and produces a single combined result set, effectively reversing the normalization split for the duration of one query.
The matching condition, written in the ON clause, is what defines "related." Almost always it's a foreign key equalling a primary key — orders.user_id = users.id — because that's the relationship the schema was designed around. You can join on any predicate, but equality on key columns is the overwhelmingly common case, and it's the one indexes are built to accelerate.
So the mental model is: normalization *separates* data to keep it clean and non-redundant; joins *recombine* it on demand to answer questions. Every non-trivial query against a well-designed relational schema involves joins, which is why understanding exactly which rows each join keeps is foundational.
The four join types and which rows survive
All four joins start from the same idea — pair left rows with right rows wherever the ON condition holds — and differ *only* in how they treat rows that find no match. The Venn-diagram picture is the clearest mental model.
- INNER JOIN keeps only the intersection: rows that match on both sides. A user with no orders, or an order with no user, disappears from the result. INNER never invents NULLs; every column in every output row came from a real matching row. It's the default and by far the most common.
- LEFT JOIN keeps all left rows plus their matches. Where a left row has a matching right row, the right columns are attached; where it doesn't, the right columns are NULL. This answers "all users, and their orders if any" — including users with zero orders.
- RIGHT JOIN is the mirror: all right rows, matched left columns attached, NULLs where the left has no match. It's logically identical to a LEFT JOIN with the tables swapped, so it's rarely used in practice.
- FULL OUTER JOIN keeps everything from both sides — the union. Matched rows combine; unmatched rows from either side appear NULL-filled on the other side's columns.
A vital practical consequence: outer joins introduce NULLs, and those NULLs change how later filters behave. Putting a condition on the right table in the WHERE clause of a LEFT JOIN quietly turns it back into an INNER JOIN, because NULL fails the comparison and those padded rows get filtered out. To preserve the outer behaviour, that condition belongs in the ON clause instead. This single subtlety is behind a large fraction of join bugs.
How the engine actually builds the result: join algorithms
The SQL keywords describe *which rows* you want; they say nothing about *how* the engine produces them. The query optimizer picks a physical join algorithm, and the same query can run thousands of times faster or slower depending on that choice. Three algorithms cover almost everything.
Nested loop join is the simplest: for each row in the outer table, scan the inner table for matches. Naively that's O(n × m), which is disastrous on large tables — but if the inner table has an index on the join column, each inner lookup becomes an O(log m) index seek instead of a full scan. Indexed nested loops are excellent when the outer side is small and the inner side is indexed, which is why joining on a foreign key that has an index is so fast.
Hash join handles large, unindexed, equality joins. The engine builds an in-memory hash table on the join key of the smaller (build) table, then streams the larger (probe) table through it, hashing each row's key to find matches in O(1). It reads each table once — roughly O(n + m) — but needs enough memory to hold the build side, spilling to disk if it doesn't fit. Hash joins only work for equality conditions.
Merge join shines when both inputs are already sorted on the join key (often because they're read in order from an index). It walks the two sorted streams in lockstep like merging two ordered lists, advancing whichever side is behind. It's very efficient and memory-light, but it depends on the sorted order being available cheaply.
Understanding these explains real behaviour: why an index transforms a slow join, why the optimizer's row-count *estimates* matter (pick the wrong build side and a hash join thrashes), and why joining two enormous tables is fundamentally more expensive than joining a small table to a large indexed one.
Trade-offs, pitfalls, and performance
Joins are powerful but not free, and several failure modes recur often enough to name.
The most dangerous is the accidental cross join (Cartesian product). If you forget the ON condition, or your condition doesn't actually relate the tables, every left row pairs with every right row and the result explodes to n × m rows. On two million-row tables that's a trillion rows — a query that never returns. Always confirm the join condition genuinely matches on keys.
Fan-out is subtler. Joining one order to its many line items multiplies the order's row once per line item, which quietly inflates any SUM or COUNT over the order's columns — a classic source of double-counted totals. The fix is usually to aggregate the many-side first, then join.
Performance-wise, the levers are:
- Index the join columns. An unindexed foreign key forces the engine into scans or large hash builds. This is the single highest-leverage fix for slow joins.
- Filter early. A
WHEREthat shrinks a table *before* it's joined means fewer rows to match; the optimizer often does this "predicate pushdown" for you, but selective conditions help it. - Join fewer, narrower rows. Selecting only needed columns and joining only necessary tables keeps intermediate result sets small, since each join's output feeds the next.
There's also a design trade-off with normalization itself: heavily normalized schemas require more joins to answer a query, and past a point, read-heavy systems denormalize — duplicating some columns to avoid a join — accepting redundancy for speed.
Joins in practice and how to reason about them
In real applications, joins are the workhorse of every reporting query, dashboard, and API endpoint that combines entities. Learning to reach for the right one quickly comes down to a few reliable habits.
Start from the question, then pick the join by what you must not lose. "Orders with their customer" is an INNER JOIN — an order without a customer is meaningless. "All customers and their order counts, including customers with none" demands a LEFT JOIN, because those zero-order customers are exactly the rows you'd otherwise drop. "Records in either system but missing from the other" is a FULL OUTER JOIN. Naming the rows you refuse to lose almost always determines the join type directly.
Read the execution plan when a join is slow. EXPLAIN shows which algorithm the engine chose — nested loop, hash, or merge — and its row estimates. A hash join spilling to disk, or a nested loop over a large unindexed inner table, points straight at the fix (add an index, or update statistics so the estimates are realistic).
Watch multi-way joins. Joining five tables means the optimizer must choose a join order, and the number of possible orders grows explosively. Good statistics and indexes let it find a plan that keeps each intermediate result small; stale statistics can send it down a plan that materialises millions of intermediate rows.
Ultimately joins tie the whole database story together: they're the reason normalization is affordable, the place indexes pay off most dramatically, and one of the biggest jobs the query optimizer does on your behalf. Mastering them is mastering how relational data is actually used.
INNER JOIN returns only rows with a match in both tables — the intersection.
LEFT JOIN returns all rows from the left table plus matched right rows; unmatched right columns become NULL.
RIGHT JOIN is the mirror: all rows from the right table plus matched left rows, NULLs otherwise.
FULL OUTER JOIN returns every row from both tables, NULL-filling wherever a side has no match.
The join condition (ON) decides what 'matches' — usually a foreign key equalling a primary key.
Quick quiz
A LEFT JOIN returns a row with NULLs in the right-table columns. What does that mean?
Which join returns only rows present in both tables?
How a SQL query executes
The pipeline that turns a text string into rows fetched off disk.
The 5 normal forms
A ladder of rules that squeezes redundancy out of a schema, one dependency at a time.
Database index types
The data structures that turn a full-table scan into a pinpoint lookup.