SQL-01-009-P · Primer — Combining Tables with INNER JOIN
The idea in one sentence
Your data lives split across two tables — customers in one, their orders in another — and INNER JOIN stitches them back together by matching each order to its customer on a shared key, keeping only the pairs that actually match on both sides.
Why it exists
Good databases deliberately keep related data in separate tables. You store each customer once in a customers table, and every order they place in a separate orders table that points back with a customer_id. This is called normalization, and it's how you avoid re-typing "Ana, Lima, [email protected]" onto all forty of Ana's order rows — you write her once and let the orders reference her. That pointer, orders.customer_id referring to customers.id, is a foreign key relationship: the foreign key (customer_id) is the column that names a row in the other table.
Splitting the data keeps it clean and correct, but now the human question — "show me each order with the name of the customer who placed it" — spans two tables at once, and no single table has the whole answer. SELECT from orders alone gives you a bare customer_id like 1, not "Ana." SELECT from customers alone gives you names but no orders. You need to recombine the halves.
INNER JOIN is that recombination. It walks the pointer for you: for every order it looks up the matching customer by the key, glues the two rows side by side into one wide row, and hands you a result that reads like the un-split table you were picturing all along — order number, amount, and customer name — without ever storing the name forty times.
How it works
You name a second table with INNER JOIN and tell SQL how the two connect using an ON clause — the join condition — that spells out which columns must be equal for two rows to count as a match. SQL then pairs every row on the left with every row on the right where the condition holds, and emits one combined row per matching pair. "Inner" means only matches survive: a customer with no orders, or an order whose customer_id points at nobody, is silently dropped — it had no partner on the other side. Because both tables have an id column, you disambiguate with table aliases (customers AS c, orders AS o) and then write c.id, o.amount so it's unambiguous which table each column comes from. Here we join a customers table to an orders table:
CREATE TABLE customers (id INTEGER, name TEXT, city TEXT);
INSERT INTO customers VALUES
(1, 'Ana', 'Lima'),
(2, 'Ben', 'Oslo'),
(3, 'Cleo', 'Cairo'); -- Cleo has NO orders
CREATE TABLE orders (id INTEGER, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES
(10, 1, 40),
(11, 1, 70),
(12, 2, 90),
(13, 4, 25); -- customer_id 4 exists in NO customer
SELECT c.name AS customer,
o.id AS order_id,
o.amount AS amount
FROM customers AS c -- alias c
INNER JOIN orders AS o -- alias o
ON o.customer_id = c.id -- the join condition (foreign key = key)
ORDER BY o.id;
-- customer order_id amount
-- -------- -------- ------
-- Ana 10 40 Ana matched twice: she has two orders
-- Ana 11 70
-- Ben 12 90
-- (Cleo is dropped: no order matched her id)
-- (order 13 is dropped: its customer_id 4 matched no customer)Ana appears on two output rows because she has two orders, and that's exactly right — the join produces one row per matching pair. Cleo vanishes because no order pointed at her, and order 13 vanishes because its customer_id = 4 pointed at a customer that doesn't exist; neither had a partner, so INNER dropped both.
The one gotcha
Forget the ON condition — or make it wrong — and you don't get an error, you get a cross join: every row on the left paired with every row on the right. With 3 customers and 4 orders, a missing/incorrect join condition produces 3 × 4 = 12 nonsense rows (each order glued to every customer), and on real tables of a million rows each that's a trillion-row explosion that hangs the query. The join condition is not optional decoration — it's the whole point, the rule that says which pairs are meaningful. The second half of this gotcha is quieter: INNER JOIN silently drops unmatched rows, which is sometimes not what you want. If you ask "how many orders does each customer have?" with an inner join, Cleo — who has zero orders — won't appear at all, so a naive count of customers-with-orders quietly excludes her and your report undercounts. That's a feature when you truly want only matched pairs, and a trap when you meant "all customers, orders where they exist" (that's a left join, a later lesson). The habit that keeps you safe: always write the ON condition, always match the foreign key to the key it references, and ask yourself whether dropping the unmatched rows is really what the question wants.
See it — how INNER JOIN pairs rows across two tables
flowchart TB
subgraph L["customers (left)"]
A1["id 1 · Ana"]
A2["id 2 · Ben"]
A3["id 3 · Cleo"]
end
subgraph R["orders (right)"]
B1["order 10 · cust 1"]
B2["order 11 · cust 1"]
B3["order 12 · cust 2"]
B4["order 13 · cust 4"]
end
J{"ON o.customer_id = c.id<br/>keep only matches"}
A1 --> J
A2 --> J
A3 --> J
B1 --> J
B2 --> J
B3 --> J
B4 --> J
J --> K["Ana + order 10"]
J --> M["Ana + order 11"]
J --> N["Ben + order 12"]
J -. "no match" .-> X["Cleo dropped<br/>order 13 dropped"]
The one-line takeaway
INNER JOIN ... ON matches rows across two tables by a shared key — usually a foreign key pointing at the other table's key — and returns one combined row per matching pair, keeping only rows that found a partner on both sides.
Where you'll meet it
You'll meet INNER JOIN the very first time a real question spans two tables, which is almost immediately: orders need customer names, comments need post titles, employees need department names, line items need product prices. Every dashboard, invoice, and "user profile" page is quietly an inner join under the hood, walking a foreign key from one table to the row it references. It's the single most-tested topic in SQL interviews — expect "join these two tables and show X with Y," and expect the interviewer to watch for three things: do you write a real ON condition (not a comma that produces a cross join), do you alias your tables and qualify ambiguous columns like c.id vs o.id, and do you notice that INNER JOIN drops the unmatched rows? The strongest candidates say out loud which rows will disappear and why — "customers with no orders won't show up here, so if the report needs them I'd reach for a LEFT JOIN instead" — turning a mechanical join into a deliberate choice about your data.
Named concepts: INNER JOIN, join condition (ON), foreign key relationships, table aliases