SQL INNER JOIN: How to Join Two Tables (Examples)
· 7 min read
To join two tables in SQL, put both tables in the FROM clause with JOIN and tell SQL which column links them with ON. The linking column is usually an id that appears in both tables: a customer_id in orders that points to the customer_id in customers.
SELECT o.order_id, c.customer_name, o.amount
FROM orders AS o
INNER JOIN customers AS c
ON c.customer_id = o.customer_id
ORDER BY o.order_id;That is the whole pattern. The rest of this guide shows what the query returns, how to join 3 tables or more, how to join on multiple columns, and the mistakes that make a join quietly return the wrong rows.
A worked example: orders and customers
Say a supplies company keeps orders and customers in separate tables. Each order stores the id of the customer who placed it, not their name.
| customer_id | customer_name | rep_id |
|---|---|---|
| 1 | Acme Dental | 10 |
| 2 | Birch Cafe | 11 |
| 3 | Cobalt Labs | 10 |
| 4 | Dune Studio | 11 |
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2025-01-04 | 120.00 |
| 102 | 3 | 2025-01-05 | 75.50 |
| 103 | 1 | 2025-01-09 | 48.00 |
| 104 | 2 | 2025-01-11 | 210.00 |
| 105 | 7 | 2025-01-12 | 99.00 |
Running the query above returns:
| order_id | customer_name | amount |
|---|---|---|
| 101 | Acme Dental | 120.00 |
| 102 | Cobalt Labs | 75.50 |
| 103 | Acme Dental | 48.00 |
| 104 | Birch Cafe | 210.00 |
Three things to notice:
- Acme Dental appears twice because it placed two orders. A join repeats the "one" side for every match on the "many" side.
- Order 105 is gone. Its
customer_idis 7, and there is no customer 7. An inner join keeps only rows that match on both sides. - Dune Studio is gone too. It has no orders, so there is nothing to match.
What SQL INNER JOIN means
INNER JOIN and plain JOIN are the same thing. "Inner" means "only the rows where the ON condition is true for both tables". If you need to keep every customer, including the ones with no orders, you want a left join instead: see SQL LEFT JOIN explained.
Table aliases
orders AS o gives the table a short nickname. Once you have two tables, prefix every column with its alias (o.amount, c.customer_name). It makes the query readable and avoids "ambiguous column" errors when both tables have a column with the same name, like customer_id.
How to join 3 tables
To join multiple tables, add one JOIN ... ON per extra table. Each new table links to one that is already in the query. Here a third table, sales_reps, is linked through the rep_id on customers.
| rep_id | rep_name |
|---|---|
| 10 | Maya |
| 11 | Jonah |
SELECT o.order_id, c.customer_name, r.rep_name, o.amount
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN sales_reps AS r ON r.rep_id = c.rep_id
ORDER BY o.order_id;| order_id | customer_name | rep_name | amount |
|---|---|---|---|
| 101 | Acme Dental | Maya | 120.00 |
| 102 | Cobalt Labs | Maya | 75.50 |
| 103 | Acme Dental | Maya | 48.00 |
| 104 | Birch Cafe | Jonah | 210.00 |
Read it as a chain: orders to customers, customers to reps. There is no direct link between orders and reps, and you don't need one.
Join multiple tables, then group
Joins are usually a step towards a summary. Add GROUP BY on top of the same three tables to get sales per rep:
SELECT r.rep_name, COUNT(*) AS orders, SUM(o.amount) AS total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN sales_reps AS r ON r.rep_id = c.rep_id
GROUP BY r.rep_name
ORDER BY r.rep_name;| rep_name | orders | total |
|---|---|---|
| Jonah | 1 | 210.00 |
| Maya | 3 | 243.50 |
More on that pattern in GROUP BY multiple columns.
How to join on multiple columns
Sometimes one column is not enough to identify a match. A targets table and a sales table may both have one row per region per quarter. The match is "same region and same quarter", so both go in the ON clause with AND.
| region | quarter | target |
|---|---|---|
| North | 1 | 5000 |
| North | 2 | 6000 |
| South | 1 | 4000 |
| South | 2 | 4500 |
| region | quarter | sales |
|---|---|---|
| North | 1 | 5200 |
| North | 2 | 5700 |
| South | 1 | 4100 |
| South | 2 | 4300 |
SELECT t.region, t.quarter, t.target, s.sales
FROM region_targets AS t
JOIN region_sales AS s
ON s.region = t.region
AND s.quarter = t.quarter
ORDER BY t.region, t.quarter;| region | quarter | target | sales |
|---|---|---|---|
| North | 1 | 5000 | 5200 |
| North | 2 | 6000 | 5700 |
| South | 1 | 4000 | 4100 |
| South | 2 | 4500 | 4300 |
Four rows in, four rows out. That is what a correct one-to-one join looks like.
When the columns have the same names in both tables, USING (region, quarter) is a shorter way to write the same join. It works in Postgres, MySQL, DuckDB and SQLite, but not in SQL Server, which only supports ON.
Common mistakes
Joining on only part of the key (a classic AI mistake)
Ask an AI assistant to "compare targets with sales by region" and it will often write ON s.region = t.region and stop there. The query runs without an error, which is what makes it dangerous:
SELECT t.region, t.quarter AS target_q, s.quarter AS sales_q, t.target, s.sales
FROM region_targets AS t
JOIN region_sales AS s
ON s.region = t.region
ORDER BY t.region, t.quarter, s.sales;| region | target_q | sales_q | target | sales |
|---|---|---|---|---|
| North | 1 | 1 | 5000 | 5200 |
| North | 1 | 2 | 5000 | 5700 |
| North | 2 | 1 | 6000 | 5200 |
| North | 2 | 2 | 6000 | 5700 |
| South | 1 | 1 | 4000 | 4100 |
| South | 1 | 2 | 4000 | 4300 |
| South | 2 | 1 | 4500 | 4100 |
| South | 2 | 2 | 4500 | 4300 |
Every target row matched every sales row in its region, so 4 rows became 8. Sum sales from this and you double it. In real data the multiplier is rarely that tidy, so the total just looks a bit high.
How to spot it: count rows before and after. If region_targets has 4 rows and you expect one match each, the join should return 4. If it returns more, your ON clause is missing a column.
Joining on the wrong column
Another AI favourite is linking two id columns that look alike but mean different things, such as products.product_id = order_items.line_id. Both are small integers, so many rows still match and you get numbers back. They are just the wrong numbers. Check every ON line by asking: "does this column in table A actually store the id from table B?" If the result is much smaller or larger than you expected, look at the join first.
Forgetting that inner joins drop rows
Order 105 above vanished without a warning. If the question is "total of all orders", an inner join to customers silently loses the orphan rows. Compare SUM(amount) on orders alone with the joined total. If they differ, decide on purpose whether the missing rows belong in the answer.
FAQ
How do I join two tables in SQL without a common column?
You need some column or rule that says which rows belong together. If there is truly no link, CROSS JOIN pairs every row with every row, which is rarely what you want for analysis. Usually the link exists through a third table, so you join 3 tables in a chain.
What is the difference between JOIN and INNER JOIN?
None. JOIN on its own means INNER JOIN in every major database. Writing INNER is just more explicit.
Can you join on multiple conditions?
Yes. Put them in the ON clause with AND, as in the region and quarter example. You can also add non-equality conditions there, such as a date range, but keep the key columns first.
Does the order of tables in a join matter?
For an inner join, no: orders JOIN customers and customers JOIN orders return the same rows. For a left join it matters a lot, because the left table is the one whose rows are all kept.
Why does my join return duplicate rows?
Because a row in one table matches more than one row in the other. Sometimes that is correct (one customer, many orders). Sometimes it means the ON clause is missing a column. Count rows before and after the join to tell which. If you need the number of distinct orders after a join, see COUNT DISTINCT in SQL.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Your first JOIN” →