Lesson 1 of 6
Inner & Outer Joins
"The whole point of a relational database is that the relationships are computed when you ask, not frozen when you store." — a paraphrase of Codd's relational algebra, and the idea that makes joins possible
Everything you have queried so far lived in a single table. Real data does not. A customer is one thing; their orders are another; the products, payments, shipments, and reviews are all their own things, each in its own focused table. That splitting is deliberate — it is the discipline that keeps data clean, non-redundant, and trustworthy. But it leaves a question hanging over every real query you will ever write: if the data is scattered across tables, how do I bring it back together?
The answer is the join — arguably the single most important operation in all of SQL, and without question the most tested. A join takes two tables and stitches their rows together on a condition you specify, usually matching a foreign key in one table to a primary key in another. Master joins and the whole relational model finally clicks: you see why we split data apart in the first place (so each fact lives in exactly one place) and how we reunite it on demand (so no power is lost).
This chapter is the doorway to Part III. By the end you will read and write INNER, LEFT, RIGHT, and FULL OUTER joins fluently; you will know the difference cold; and you will have the one query pattern that trips up more candidates than any other — find the rows that have no match — permanently in your toolkit.
When data is deliberately split across tables, how do we recombine it — and how do we control what happens to rows on one side that have no match on the other?
The mental model: matching rows on a condition
A join looks at two tables side by side and asks, for each pair of rows, do these belong together? The test you give it — the join condition — is almost always "the foreign key over here equals the primary key over there." In our seed data, orders.customer_id points at customers.id. That single shared value is the thread we pull to reconnect the two tables.
Start with the workhorse, the INNER JOIN. It keeps only the pairs that match — every order lined up with the customer who placed it:
Click Run to execute the query.
Six rows come back — one per order — each carrying the customer's name alongside the product. The engine walked the two tables, kept every (customer, order) pair where customers.id = orders.customer_id, and discarded everything else. That "discarded everything else" is the defining trait of an inner join: no match, no row.
Analogy Picture two decks of index cards — one card per customer, one per order — with a customer number written on every order card. An inner join is you pairing up cards that share a number and setting the pairs on the table. Any card with no partner gets swept into the bin. A left join (coming up) is the same game, except you promise to keep every customer card face-up even if no order matches it — you just leave the order half of the pair blank.
Aliases and qualified names — write joins like a professional
Spelling out customers. and orders. before every column gets tiring fast, and in a five-table join it becomes unreadable. SQL lets you give each table a short alias and use it everywhere. Note also that the word INNER is optional — a bare JOIN is an inner join, and that is how most engineers write it:
Click Run to execute the query.
Two things are worth internalizing here. First, once you alias customers AS c, you must refer to it as c for the rest of the statement — the long name is gone. Second, qualifying columns (c.name, o.amount) is not just tidiness. When both tables have a column of the same name — imagine both had a created_at — an unqualified reference is ambiguous and the query fails. Qualify early and the problem never arises.
Outer joins: keeping the unmatched rows
An inner join answers "which rows go together?" But a huge fraction of real questions are the opposite: "which rows don't?" Which customers have never ordered? Which products have never sold? Which employees manage nobody? To answer those, you must keep rows that have no match — and that is what an outer join does.
LEFT JOIN — keep everything on the left
A LEFT JOIN (the full name is LEFT OUTER JOIN; the OUTER is optional and usually dropped) returns every row from the left table. Where a matching right-side row exists, you get it; where none exists, the right-side columns come back as NULL.
Every customer in our seed happens to have at least one order, so to see the effect we need a customer with none. Because the playground rebuilds a fresh database on every run, we can safely insert one inline first — it vanishes the moment you run something else:
Click Run to execute the query.
Seven rows now. Ada appears twice (two orders), everyone else once — and Rex appears with NULL for product and amount. That is the left join keeping its promise: the left table is preserved whole, and the absent right side is filled with nulls. Swap LEFT JOIN back to JOIN and Rex vanishes; that one word is the entire difference.
The pattern that wins SQL screens — the anti-join: "Find the customers who have never placed an order" is the most common interview join question there is. The trick: do a LEFT JOIN, then keep only the rows where the right side came back NULL — those are exactly the left rows that found no partner.
Click Run to execute the query.
Only Rex comes back. Read the logic slowly: the LEFT JOIN gives every customer a row (nulls where there's no order); the WHERE o.id IS NULL then filters down to precisely the customers whose order side is empty. Always test the null on a column that cannot legitimately be null for a real match — a primary key like o.id is perfect. This is called an anti-join, and it is worth memorizing as a shape, not a sentence.
RIGHT JOIN — keep everything on the right
A RIGHT JOIN is the mirror image: it keeps every row from the right table, filling the left side with nulls where there's no match. Anything a right join does, a left join can do by simply swapping the two table names — which is why left joins dominate in practice and many engineers rarely write RIGHT. But you must be able to read it. Here we keep every customer by putting customers on the right:
Click Run to execute the query.
Same seven-row result as the left join above — Rex included with a null product — because "keep all customers" is what we asked for, just phrased from the other direction.
A portability landmine RIGHT JOIN and FULL OUTER JOIN only arrived in SQLite in version 3.39 (2022). Our playground (SQLite 3.41) supports them, and so do MySQL, PostgreSQL, SQL Server, and Oracle. But if you meet an older SQLite build — embedded in a phone app, say, or a legacy system — those keywords throw a syntax error, and the workaround is to rewrite the right join as a left join (swap the tables) and simulate a full outer join with LEFT JOIN ... UNION ... anti-join. Knowing the rewrite is a genuine senior-level party trick.
FULL OUTER JOIN — keep everything on both sides
A FULL OUTER JOIN keeps all rows from both tables: matched pairs where they exist, left rows with a null right side where the right has no match, and right rows with a null left side where the left has no match. To show all three cases at once, we add both an order-less customer and an orphan order (one whose customer_id points at nobody):
Click Run to execute the query.
Look for the two loners in the result: Rex (a customer with no order — product is null) and Ghost (an order whose customer 99 doesn't exist — name is null). Everything that matched shows up in the middle. A full outer join is the tool for reconciliation questions: "show me everything on both sides and flag what didn't line up" — comparing two systems, auditing for orphans, diffing a snapshot.
Here is the whole family in one table — commit it to memory:
| Join type | Keeps left rows with no match? | Keeps right rows with no match? | Typical question it answers |
|---|---|---|---|
INNER JOIN |
No | No | "Customers and their orders" |
LEFT [OUTER] JOIN |
Yes (right = NULL) | No | "All customers, orders if any" |
RIGHT [OUTER] JOIN |
No | Yes (left = NULL) | "All orders, customer if any" |
FULL OUTER JOIN |
Yes | Yes | "Everything, flag the mismatches" |
ON versus USING
Every join above used ON, the general form: you write any boolean condition, and it need not be equality — ON a.x >= b.y is legal (that's a non-equi join). But there is a common shortcut. When the two tables share a join column with the exact same name, you can write USING (column) instead. Our seed tables can't demonstrate it — the keys are named id and customer_id, deliberately different — so here's a tiny inline pair that can:
Click Run to execute the query.
Two things USING does that ON does not: the shared column appears once, unqualified in the result (notice we wrote plain dept_id, not emp.dept_id), and the syntax is shorter. Its cost is rigidity — it only works when the names match exactly and you want plain equality. Most production codebases standardize on ON for consistency and readability; reach for USING only when the column names genuinely align.
Joins in real queries: WHERE, GROUP BY, and aggregation
Joins rarely travel alone. The everyday shape of an analytical query is join the tables, filter, group, aggregate — the full clause pipeline working together. Here is "how many orders and how much has each customer spent," which combines a LEFT JOIN (so zero-order customers still appear) with GROUP BY and aggregates:
Click Run to execute the query.
Rex lands at the bottom with 0 and 0, exactly as he should. This block hides a subtle, heavily tested point, unpacked next.
Why COUNT(o.id) and not COUNT(): After a LEFT JOIN, an unmatched customer still produces one row — with nulls on the order side. COUNT() counts rows, so it would report 1 for Rex even though he has no orders. COUNT(o.id) counts non-null values of the order key, so it correctly reports 0. Run this and read Rex's two numbers:
Click Run to execute the query.
Rex shows count_star = 1 but count_orders = 0. When you count "how many related things exist" after an outer join, count a non-nullable column from the right table, never *. This one distinction separates a correct report from a quietly wrong one.
Joining more than two tables
Nothing limits you to two tables. You chain joins left to right, each JOIN ... ON bolting one more table onto the growing result. To show a three-table join we add a small shippers table inline and connect customers → orders → shippers:
Click Run to execute the query.
Three rows — only the orders that were actually shipped (1, 3, 5) survive, because all three joins here are inner. Change the second JOIN to a LEFT JOIN and every order reappears, with a null carrier for the unshipped ones. Real reporting queries routinely chain five, eight, a dozen tables this way; the mechanics never change — one ON clause per table, matching keys as you go.
In production Joins are where ORMs quietly betray you. Fetch a list of customers and then, in a loop, ask for each one's orders, and you have committed the infamous N+1 query problem — 1 query for the customers plus N more, one per customer. On 10,000 customers that's 10,001 round-trips to the database, each with its own latency. A single JOIN (or the ORM's eager-loading / "include" option, which emits one) does it in one round-trip. Learning to see the join the ORM should have written is one of the highest-leverage database skills you can develop.
Common Mistakes
- Forgetting the
ONclause. Omit the join condition and many engines give you a cross join — every left row paired with every right row (a Cartesian product). Five customers × six orders = 30 nonsense rows. Always state how the tables relate. - Using an inner join when you needed a left join. If any customer might have zero orders and you inner-join, those customers silently disappear from your report. "My totals are missing people" is almost always a left-vs-inner bug.
- Counting
*after an outer join. As shown above,COUNT(*)counts the null-filled placeholder rows. Count a non-nullable right-side column instead. - Putting the right-table filter in
WHEREand accidentally turning a left join into an inner join. WritingLEFT JOIN orders o ON c.id = o.customer_id WHERE o.amount > 1000throws away the null rows (null is not> 1000), quietly undoing theLEFT. If the filter must apply only to matched rows and keep the unmatched ones, move it into theON:... ON c.id = o.customer_id AND o.amount > 1000. - Ambiguous column names. Selecting
idwhen both tables have anidfails. Qualify every column in a join, always. - Comparing to
NULLwith=.WHERE o.id = NULLnever matches anything — null is the unknown, and nothing equals unknown. The anti-join test must beIS NULL, not= NULL.
Predict the output Before running it, decide how many rows this returns and why:
Click Run to execute the query.
Ada (Lagos) has two orders and Sam (Lagos) has one; Lin, Zoe, and Tom aren't in Lagos. So: three rows. Now change INNER to LEFT — does the count change? (It doesn't — both Lagos customers already have orders, so there are no nulls to preserve. The LEFT only matters when a kept row has no match.) Understanding why the answer is unchanged is the real test.
INNER JOIN and a LEFT JOIN?NULL where there is no match. Inner drops the unmatched; left keeps them.sql
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.id IS NULL;
`
Left-join the two tables, then keep only the rows where the order side is null — those are precisely the customers with no matching order. (Equally valid: WHERE c.id NOT IN (SELECT customer_id FROM orders) — but mind that NOT IN` with any null in the subquery returns no rows, a classic trap the join avoids.)customers, orders, and order_items. Write one query returning each customer's name and their total lifetime spend, including customers who have spent nothing — and explain why `COUNT( would be wrong for an order count.* — Chain left joins from customers outward so zero-order customers survive, aggregate with SUM, and wrap it in IFNULL/COALESCE so "no spend" reads as 0 rather than NULL:
`sql
SELECT c.name, IFNULL(SUM(oi.qty * oi.price), 0) AS lifetime_spend
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
LEFT JOIN order_items AS oi ON o.id = oi.order_id
GROUP BY c.id, c.name;
`
COUNT(*) would count the null-filled placeholder row an outer join creates for a customer with no orders, reporting 1 instead of 0; count a non-nullable column from the right table (COUNT(o.id)) instead. Bonus points for noting that chaining left joins can multiply rows — a customer with 3 orders of 2 items each yields 6 rows — so SUM` must aggregate at the grain you actually intend.WHERE filter on the right table silently converts a left join to an inner one.Hands-on Build one report that exercises every idea in this chapter. Starting from the block below: (1) run it to see spend per city; (2) add an order-less customer inline (as we did above) and switch the inner join to a LEFT JOIN so a "no-sales" city could appear; (3) add a HAVING clause to show only cities whose total spend exceeds 5000; (4) as a stretch, add a third inline table mapping each city to a region and join it in, then group by region instead.
Click Run to execute the query.
Summary
- A join stitches two tables together on a condition — almost always foreign key = primary key — recombining data that was deliberately split apart.
INNER JOINkeeps only matching pairs; no match, no row. A bareJOINmeans inner.LEFT JOINkeeps every left row, filling the right side withNULLwhere there's no match;RIGHT JOINmirrors it;FULL OUTER JOINkeeps unmatched rows from both sides.- The anti-join —
LEFT JOIN ... WHERE right.key IS NULL— finds rows with no match, and is the single most-tested join pattern. - Use aliases and qualify columns to keep multi-table queries readable and unambiguous;
USINGis a shortcut for identically named join columns,ONis the general form. - After an outer join, count a non-nullable right-side column, not
*, and beware that a right-table filter inWHEREquietly demotes a left join to an inner one. RIGHT/FULL OUTERjoins are modern SQLite (3.39+) and universal in MySQL/Postgres — know the left-join rewrite for older engines.
Joins answer "which rows go together?" — but they leave a bigger question open: what if the value you want to match on comes from another query entirely? Next we descend into subqueries, where a query becomes an ingredient inside another query.
Editorial reflection There is a quiet philosophy hidden in the join. We split the world into small, honest tables — one fact in one place, never repeated — precisely because we trust ourselves to reunite them whenever we ask. The join is the payoff for that discipline: normalization is the promise, and the join is where the promise is kept. Engineers who fear joins tend to denormalize prematurely, copying data everywhere "to be safe," and inherit the far worse problem of keeping every copy in sync. Learn to join well and you earn the freedom to model data cleanly — which is, in the end, the whole art of the relational database.
Q&A · 0
Enrol to ask questions and join the discussion.
No questions yet — be the first to ask.