INNER JOIN — Combining Related Tables

So far every query has read from a single table. But the interesting questions in any application span multiple tables — "which user placed this order?", "which products are in this cart?", "how much has Alice spent?". The answer to all of those is a JOIN: combine rows from two (or more) tables based on a shared identifier. INNER JOIN is by far the most common kind, and the rest of this chapter builds on it.

The problem JOINs solve

Look at a single row from our orders table:

| id | user_id | total  | status     | created_at          |
|----|---------|--------|------------|---------------------|
| 1  | 1       | 145.00 | completed  | 2026-04-20 14:30:21 |

That tells you order #1 was placed by user 1, but not who user 1 is. To turn user_id = 1 into the human-readable Alice Khan, you need to look up user 1 in the users table. Doing that lookup inside the SQL — and getting a single combined result — is exactly what JOIN is for.

Your first INNER JOIN

SELECT users.name, orders.id, orders.total, orders.status
FROM orders
INNER JOIN users ON orders.user_id = users.id;

Result:

| name        | id | total  | status     |
|-------------|----|--------|------------|
| Alice Khan  | 1  | 145.00 | completed  |
| Bob Patel   | 2  |  48.50 | completed  |
| Bob Patel   | 3  | 320.00 | shipped    |
| Dan Müller  | 4  |  63.00 | pending    |
| Esha Roy    | 5  |  25.00 | cancelled  |

Three pieces deserve a closer look:

FROM orders — the left table; the one you're starting from.
INNER JOIN users — the right table; the one you're joining in.
ON orders.user_id = users.id — the match condition; how the database connects rows from one to the other.

Read it as English: "Take every order, find the user whose id matches the order's user_id, and merge the columns." The result has columns from both tables in the same row.

Table aliases — shorter, more readable

orders.user_id = users.id gets repetitive in larger queries. SQL lets you rename tables for the duration of the query:

SELECT u.name, o.id, o.total, o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

Same query, less typing. The o and u aliases hold for the entire query — SELECT, WHERE, ORDER BY — anywhere a table name might appear. You'll see professional SQL almost always written with aliases.

"Inner" — only matched rows survive

INNER JOIN's defining behaviour: a row from either side is only in the result if it has a match on the other side. In our data Carol Singh has no orders, so she doesn't appear:

INNER JOIN keeps only the rows that match on both sides

users (left)

1 — Alice
Has orders → kept
2 — Bob
Has orders → kept
3 — Carol
No orders → dropped
4 — Dan
Has order → kept
5 — Esha
Has order → kept

orders (right)

1 — user_id 1
Matches Alice → kept
2 — user_id 2
Matches Bob → kept
3 — user_id 2
Matches Bob → kept
4 — user_id 4
Matches Dan → kept
5 — user_id 5
Matches Esha → kept

INNER JOIN result

Alice + order 1
Bob + order 2
Bob + order 3
Dan + order 4
Esha + order 5
(Carol absent — no orders)
Dropped because no match

If you want Carol included despite having no orders, you need a LEFT JOIN — that's the next lesson.

Joining three tables

JOINs chain. To answer "which products were in each order, by which user?" you join three tables — orders, order_items, products, plus users for names — in one query:

SELECT
    u.name           AS customer,
    o.id             AS order_id,
    p.name           AS product,
    oi.quantity,
    oi.price
FROM order_items oi
INNER JOIN orders   o ON oi.order_id   = o.id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN users    u ON o.user_id     = u.id
ORDER BY o.id, p.name;

Result (sample):

| customer    | order_id | product                  | quantity | price  |
|-------------|----------|--------------------------|----------|--------|
| Alice Khan  | 1        | Mechanical Keyboard Pro  | 1        | 120.00 |
| Alice Khan  | 1        | USB-C Hub                | 1        |  45.00 |
| Alice Khan  | 1        | Wireless Mouse           | 1        |  25.00 |
| Bob Patel   | 2        | Clean Code (book)        | 1        |  30.00 |
| Bob Patel   | 2        | Desk Lamp                | 1        |  42.00 |
| Bob Patel   | 2        | SQL for Testers (book)   | 1        |  18.50 |

A pattern to notice: each JOIN line says "this is what connects the new table to something already in the query." oi.order_id = o.id connects order_items to orders. oi.product_id = p.id connects order_items to products. o.user_id = u.id connects orders to users. Read the ON clauses, and the schema diagram practically draws itself.

Adding WHERE to a JOIN

Filters apply to the joined result. You can filter on columns from any table:

SELECT u.name, o.total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
  AND u.role = 'admin';

Result:

| name       | total  |
|------------|--------|
| Alice Khan | 145.00 |

One row — Alice is our only admin, and her one completed order shows up. If you want all admins' completed orders, this is exactly the shape of query you'd write.

A real QA verification

After a UI test where Bob clicks through and places an order with a Wireless Mouse and a Clean Code book, you want to verify that exactly those two products are linked to the new order:

SELECT u.name, p.name AS product, oi.quantity
FROM order_items oi
INNER JOIN orders   o ON oi.order_id   = o.id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN users    u ON o.user_id     = u.id
WHERE u.email = 'bob@example.com'
ORDER BY o.created_at DESC;

Read the result and compare against what the UI showed. If the rows match the cart, the order saved correctly. If a product is missing, the API didn't insert that line item. If an extra product appears, the cart contained a stale item the UI didn't show. Three failure modes, one query, full diagnosis.

⚠️ Common Mistakes

JOIN without ON. INNER JOIN users with no ON clause produces a Cartesian product — every order combined with every user. Five orders and five users gives you 25 garbage rows. Always include the ON clause.
, (comma join) with WHERE — the old style. FROM orders, users WHERE orders.user_id = users.id works but is harder to read and easy to mess up (forget the WHERE and you get a Cartesian product). Always use explicit INNER JOIN ... ON.
Forgetting that INNER JOIN drops unmatched rows. If a tester runs SELECT u.name FROM users u INNER JOIN orders o ON u.id = o.user_id to "list all users," users with no orders silently disappear. The right tool there is LEFT JOIN — covered next.

🎯 Practice Task

25 minutes. Use the e-commerce database. Use aliases on every query.

List every order with the user's name. Result columns: name, order_id, total, status.
Show every product with its category name. Result: product, price, category.
List every order item with the order's user, the product name, and the quantity. (Three-way JOIN: order_items, orders, products, plus users.)
Find every order placed by a tester (role = 'tester'). Show user name, order id, and total.
Find all completed orders for products in the Electronics category. (You'll need to filter both orders.status and categories.name.)
Stretch: for each order placed by Bob, list its line items (product name, quantity, price). Sort by order id, then by product name. Read the result and check whether the sum of (price × quantity) matches the recorded orders.total — bonus points if you spot the deliberate inconsistency in our seed data.

Next lesson tackles the queries INNER JOIN can't answer — the ones that need rows without a match. That's where LEFT JOIN earns its keep.