What is INNER JOIN in SQL? Joining tables for beginners

INNER JOIN (or just JOIN) is the SQL command for combining two tables by a shared key. The simplest and most common JOIN type — you can't go far with relational databases without it.

Quick analogy. You have two tables:

• users — users (id, name)
• orders — orders (id, user_id, amount)

There's no buyer name in orders — only user_id. To get "buyer name + order amount" pairs, you have to glue these tables back together by a common key — users.id matches orders.user_id. That gluing is INNER JOIN.

Why JOIN matters

A normal database splits data across tables to avoid duplication. The user's name lives only in users; orders references the user via user_id. This is called normalisation.

When you need to show "name + order", you have to assemble the data back together — that's what JOIN is for. It's one of the most important SQL topics — without JOIN you can't write a single real-world query.

Basic syntax

SELECT columns
FROM table_A
INNER JOIN table_B
  ON table_A.key = table_B.key;

The word INNER is optional — JOIN without a prefix means INNER JOIN. Most code says just JOIN.

Example: a shop

users table:

id	name	email
1	Anna	anna@example.com
2	Boris	bob@example.com
3	Vera	vera@example.com
4	Grisha	grisha@example.com

orders table:

id	user_id	amount	created_at
10	1	500	2024-03-01
11	1	1500	2024-03-05
12	2	200	2024-03-07
13	5	3000	2024-03-10

Query — join and show buyer name with amount:

SELECT users.name, orders.amount, orders.created_at
FROM users
INNER JOIN orders
  ON users.id = orders.user_id;

Result:

name	amount	created_at
Anna	500	2024-03-01
Anna	1500	2024-03-05
Boris	200	2024-03-07

What happened:

• Each row from users was paired with each row from orders where users.id = orders.user_id.
• Anna has two orders → two rows in the result, name duplicated.
• Boris has one order → one row.
• Vera and Grisha aren't there — they have no orders. That's the INNER JOIN trait: only pairs with a match in both tables.
• Order #13 isn't there — user_id = 5 doesn't exist in users. Sometimes happens, e.g. user got deleted but the order stayed.

If you also want users without orders, that's LEFT JOIN (separate article).

Aliases — short table names

Writing users.name, orders.amount every time is long. Real-world code uses aliases:

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

users u means "give the users table the alias u". Doesn't change the logic — just shorter. In big queries with three-to-five tables, queries without aliases are unreadable.

Note: aliases become mandatory if both tables have a column with the same name. Otherwise the database can't tell whose id you mean.

Joining three or more tables

JOINs chain: A JOIN B JOIN C. Add a products table:

id	name	price
100	Bosch kettle	49
101	iPhone 15	999
102	Book "Clean Code"	22

And extend orders with product_id:

id	user_id	product_id	amount
10	1	100	49
11	1	102	22
12	2	100	49

Query — who bought what:

SELECT u.name AS buyer, p.name AS product, o.amount
FROM users u
INNER JOIN orders o   ON u.id = o.user_id
INNER JOIN products p ON p.id = o.product_id;

Result:

buyer	product	amount
Anna	Bosch kettle	49
Anna	Book "Clean Code"	22
Boris	Bosch kettle	49

How it works:

1. The database first joins users and orders on user_id.
2. Then the result is joined with products on product_id.
3. The output keeps only rows where both joins matched.

These chains can be as long as you need. "Users, their orders, products in those orders, product categories, and stores" — that's five JOINs in a row.

JOIN with WHERE

JOIN usually goes with WHERE — JOIN glues tables, WHERE filters the result. E.g. Anna's orders above 1000:

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.name = 'Anna'
  AND o.amount > 1000;

Extremely common combo — almost any business query is "join the tables + filter with WHERE + maybe aggregate".

INNER JOIN is intersection

In set-theory terms: INNER JOIN is the intersection of two tables on a key. Only the rows present in both make it.

users       orders
  ┌──┐      ┌──┐
  │  │      │  │
  │ ●│ ←  → │● │  ← pairs: only these end up in the result
  │  │      │  │
  └──┘      └──┘

User with no orders — not in the result. Order with no user (broken FK) — not in the result either.

Bigger example: a library

books:

id	title	author_id
1	War and Peace	10
2	Anna Karenina	10
3	Crime and Punishment	11
4	The Idiot	11
5	(orphan — author not in DB)	99

authors:

id	full_name	country
10	Leo Tolstoy	Russia
11	Fyodor Dostoevsky	Russia
12	Erich Maria Remarque	Germany

Query — every book with author name and country:

SELECT b.title, a.full_name AS author, a.country
FROM books b
JOIN authors a ON a.id = b.author_id
ORDER BY a.full_name, b.title;

Result:

title	author	country
Anna Karenina	Leo Tolstoy	Russia
War and Peace	Leo Tolstoy	Russia
Crime and Punishment	Fyodor Dostoevsky	Russia
The Idiot	Fyodor Dostoevsky	Russia

The orphan book is gone — author_id = 99 isn't in authors. Remarque is gone — he has zero books in our books. INNER JOIN dropped both.

Common beginner mistakes

1. Forgetting the ON clause. Writing JOIN orders with no ON — most DBs either error out or fall back to a CROSS JOIN (Cartesian product, every row times every row). Either an error or a very slow query.

2. Wrong columns in ON. ON u.id = o.id instead of ON u.id = o.user_id is a classic typo. The query won't crash — it'll return nonsense (user #5 paired with order #5, which is meaningless).

3. Ambiguous column names. If both users and orders have id, and you write SELECT id, name FROM users JOIN orders ON …, you get "column reference "id" is ambiguous". Use users.id or aliases.

4. Duplicate rows in the result. If a user has 5 orders, the result has 5 rows with that user's name repeated. Not a bug — it's how JOIN works. Want unique users? Wrap in SELECT DISTINCT u.name or combine with GROUP BY.

5. Thinking JOIN itself is slow. JOINs are fast when there are indexes on the join columns. That's why FK columns (user_id and similar) are always indexed in production.

6. Old comma-style joins. Technically you can write FROM users, orders WHERE users.id = orders.user_id. Don't. The modern JOIN ... ON ... syntax reads better and clearly separates joining from filtering.

Quick recap

• INNER JOIN combines two tables by a shared key.
• Only pairs with a match in both tables make it to the result.
• The join condition goes after ON.
• The word INNER is optional; people just write JOIN.
• You can chain many JOINs (three- and five-table queries are normal).
• Use aliases (users u) to keep things readable.
• If you also want users with no orders, that's LEFT JOIN, a different scenario.

Try it yourself

On the users and orders tables above:

1. List buyer-name and amount pairs, sorted by amount descending.
2. Find all users who placed an order in March 2024.
3. Count the total order amount per user (combo JOIN + GROUP BY + SUM).

When JOIN clicks for you, it's a freeing moment in learning SQL. Before — you can read one table. After — real-world data opens up fully.