Your customers live in one table. Their orders live in another. Every real question — 'which customers ordered last week?', 'what did Maria buy?' — needs data from both at once. INNER JOIN is how you stitch those tables together, and by the end of this lesson you'll be able to predict exactly how many rows come out the other side.
What you'll learn
What
An INNER JOIN combines rows from two tables by pairing each row in the first table with every row in the second table where a join condition — usually an equality between key columns — is true. Rows with no match on the other side are dropped from the result entirely.
Why
Relational databases deliberately split data across tables to avoid duplication (a customer's email is stored once, not on every order). Joins are the price of that design: without them, you could never answer a question that spans two tables. INNER JOIN specifically answers 'give me only the rows where both sides agree'.
Where it's used
Every analytics query that touches more than one table. Order reports, revenue by customer, product performance dashboards, A/B test result tables — nearly all of them start with an INNER JOIN between a fact table (orders) and a dimension table (customers, products).
Where this runs in production
Every storefront analytics page joins the orders table to customers and line items so merchants see who bought what. An INNER JOIN keeps only completed, attributable orders — exactly the rows a revenue report should count.
Pricing and occupancy analyses join the reservations fact table to the listings dimension on listing_id. Because a listing can have many reservations, analysts must reason about one-to-many row multiplication — the core skill this lesson teaches.
Stripe's reporting infrastructure joins charge records to customer records on customer_id so finance teams can break revenue down by account, plan, and country — millions of INNER JOINs executed per day inside their data warehouse.
Picture two lines of dancers: customers on the left, orders on the right. Each order holds a card with a customer_id. The music starts, and every order walks over to the customer whose id matches its card. Customers who attract no orders — and orders holding an id no customer has — leave the floor. Only matched pairs remain. That's an INNER JOIN. The limit of the analogy: a customer with three orders dances three times, appearing in three result rows — one per partner, not one total.
The join key is the column (or columns) the ON condition compares — typically a primary key on one side (customers.customer_id, unique per customer) and a foreign key on the other (orders.customer_id, a reference pointing back at that customer). Keys must be comparable: same meaning, compatible types, consistent formatting.
SELECT c.name, o.order_id, o.total FROM customers AS c INNER JOIN orders AS o ON c.customer_id = o.customer_id;
AS c and AS o create table aliases — short nicknames used to qualify columns. Qualification (writing c.name instead of name) matters because both tables may have a column with the same name; an unqualified reference to a duplicated name raises an 'ambiguous column' error. The keyword INNER is optional — plain JOIN means INNER JOIN in every major dialect — but writing it out makes intent explicit.
In SQL, NULL = NULL evaluates to unknown, not true. A row whose join key is NULL matches nothing and silently vanishes from an INNER JOIN result. If orders.customer_id is NULL for guest checkouts, those orders will not appear in a customers-joined report — and no error tells you so. Always check key columns for NULLs before trusting join-based totals.
For an INNER JOIN, filtering in ON or in WHERE produces the same rows — the condition eliminates non-matching pairs either way. Convention: put the key match in ON, put business filters (o.status = 'shipped') in WHERE. This separation becomes semantically critical when you move to LEFT JOIN in the next lesson, so build the habit now.
Follow a row's journey: two source tables feed the matcher, which keeps only key-matched pairs. Click each node for the row-count math.
An online bookshop stores 3 customers and 3 orders. Support wants to see each order next to the customer's name instead of a bare id.
We start from customers and select one column from each table, qualifying both with the table name so it is unambiguous where each column lives. customers has 3 rows: Alice(1), Ben(2), Chloe(3).
Your task
The customers table has customer_id, name, country. The orders table has order_id, customer_id, order_date, total. Write a query returning each order's id and total next to the customer's name, for orders over $50, sorted by total descending. Fill in the blanks.
name | order_id | total -------+----------+-------- Chloe | 108 | 129.99 Alice | 105 | 63.75 Ben | 107 | 52.10 (3 rows)
Write your solution in the editor on the right, then hit Run.
An INNER JOIN between customers and orders on customer_id returns which rows?
customers has 4 rows. orders has 6 rows, each with a valid customer_id; one customer placed 3 orders, and one customer placed none. How many rows does the INNER JOIN on customer_id return?
Some rows in orders have customer_id = NULL (guest checkouts). What happens to them in an INNER JOIN with customers?
What is the most likely cause?
Using orders (order_id, customer_id, total) and customers (customer_id, name, country), return each country and its number of orders as order_count, sorted by order_count descending. Only countries with at least one order should appear — which the join guarantees on its own.
Which pair of problems explains the missing rows?
Explain what an INNER JOIN does and how it differs from a LEFT JOIN.
An INNER JOIN pairs rows from two tables wherever the ON condition is true and returns only those matched pairs — rows with no match on the other side are excluded from the result entirely. A LEFT JOIN also returns matched pairs, but additionally keeps every unmatched row from the left table, filling the right table's columns with NULL. The practical difference is the question each answers: INNER JOIN answers 'which customers placed orders?', while LEFT JOIN answers 'show me all customers, with their orders if any'. Choosing between them is a business decision, not a syntax one — an INNER JOIN in a report silently defines 'customers without orders don't exist here'. In interviews I also mention that plain JOIN means INNER JOIN, and that the choice changes row counts: INNER can only shrink or duplicate, never preserve unmatched rows.
Table A has 1,000 rows and table B has 2,000 rows. After joining on a key, is the result guaranteed to have at most 2,000 rows? Why or why not?
No — there is no such guarantee unless the key is unique on at least one side. A join's row count equals the number of matched pairs, and if the key is duplicated on both sides you get a many-to-many multiplication: 3 rows with key 'X' in A and 4 rows with key 'X' in B produce 12 result rows for that key alone. In the worst degenerate case — every row sharing one key value — the result approaches 1,000 × 2,000 = 2,000,000 rows. The safe upper bound only exists for one-to-many joins: if the key is unique in A, each B row matches at most once, capping the result at 2,000. In practice I verify this before trusting any joined aggregate: I check key uniqueness with a GROUP BY ... HAVING COUNT(*) > 1 query, and I compare the joined row count against the granular table's row count. Unexpected row explosion is the single most common cause of inflated metrics in analytics.
A stakeholder says a join-based revenue report is 'missing orders'. What would you investigate?
First I would check for unmatched join keys, because INNER JOIN drops them silently: orders whose customer_id is NULL (guest checkouts) or references a customer no longer in the customers table (deleted or filtered accounts) simply vanish. A quick diagnostic is a LEFT JOIN from orders to customers with WHERE customers.customer_id IS NULL to surface the orphans and count the missing revenue. Second, I would check key hygiene — type mismatches, trailing whitespace, case differences in text keys — since '1042 ' will never equal 1042. Third, I would look at any filters interacting with the join: a WHERE condition on a column from the joined table can quietly remove rows. Finally I would reconcile totals: SUM(total) on the raw orders table versus on the joined result tells me exactly how much revenue the join is losing. The underlying principle is that INNER JOIN is also a filter, and every filter should be deliberate.
Common Mistakes to Avoid
1) Forgetting the ON clause (or writing an always-true one) — you get a Cartesian product: every row paired with every row, and row counts in the millions. 2) Summing a parent-level column after joining to a child table — o.total appears once per line item and gets counted multiple times; aggregate at the right grain first. 3) Assuming NULL keys match: NULL = NULL is not true in SQL, so NULL-keyed rows silently disappear from INNER JOIN results. 4) Leaving column references unqualified when both tables share a name — SELECT customer_id after joining raises an ambiguity error; always write c.customer_id. 5) Treating INNER JOIN as lossless — it is also a filter, and unmatched rows on either side are dropped without any warning; reconcile row counts before trusting a joined report.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: how does an INNER JOIN decide which rows to keep?' • 'Give me two small tables and quiz me on predicting the joined row count.' • 'Show me a real bug caused by a one-to-many join inflating a SUM.' • 'Explain why NULL join keys never match, with a fresh analogy.' • 'Interview mode: ask me to compare INNER JOIN with a Cartesian product and grade my answer.'
Glossary
INNER JOIN — combines two tables, returning only rows where the join condition matches on both sides. Join key — the column(s) compared in the ON clause. ON clause — the condition that decides which row pairs belong in the result. Primary key — a column that uniquely identifies each row in its table. Foreign key — a column that references a primary key in another table. Table alias — a short nickname (customers AS c) used to qualify columns. One-to-many — a key relationship where one row on one side matches many on the other, duplicating the 'one' side in results. Cartesian product — every row of one table paired with every row of another; what you get with no join condition. Row explosion (fan-out) — unintended row multiplication caused by duplicated keys. Matched / unmatched rows — rows that do or do not find a partner under the ON condition; INNER JOIN keeps only the former.
Recommended Resources
• Docs: PostgreSQL manual, 'Joins Between Tables' in the Tutorial chapter — the canonical walk-through with runnable examples. • Read: 'A Visual Explanation of SQL Joins' (the classic Venn-diagram essay) and its follow-up critiques, which sharpen the row-pairing mental model this lesson uses. • Practice: build two tiny tables in any SQL sandbox, add a duplicate key on one side, and predict the joined row count before running — repeat until you are never surprised. • Next in DSM: LEFT & RIGHT JOINs — what happens when you want to keep the unmatched rows an INNER JOIN throws away.
Recap
✓ INNER JOIN pairs rows from two tables where the ON condition is true and drops everything unmatched — from both sides. ✓ The join key is usually a primary key matched against a foreign key; keys must be clean, comparable, and non-NULL to match. ✓ Result row count = number of matched pairs: one-to-one preserves counts, one-to-many duplicates the 'one' side per match. ✓ NULL = NULL is never true, so NULL-keyed rows silently vanish from INNER JOIN results. ✓ Qualify columns with table aliases (c.name, o.total) to avoid ambiguity errors and unreadable queries. ✓ INNER JOIN is also a filter — always reconcile joined row counts and totals against the source tables before trusting a report. Next up: LEFT & RIGHT JOINs. INNER JOIN answered 'who matched?' — next you'll keep the customers with no orders and the products never sold, using outer joins that fill the gaps with NULL instead of dropping them.
Run your code to see the output here.