Himanshu SharmaSoftware engineer
Let’s talk

MySQL · Lesson 23 of 50

INNER JOIN

Combine rows from two or more tables with INNER JOIN and ON, use table aliases, chain three or four tables, and avoid ambiguous columns, with verified examples.

16 minCombining tablesUpdated 22 October 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonWrite INNER JOIN queries that combine related tables and explain which rows appear in the result.

Why joins exist

The shop database stores customers once and orders once. An order only contains the customer_id, not the customer's name. To print "Asha Verma ordered on 5 January", you must look the name up in another table. A join does that lookup for every row.

text
orders                       customers
id | customer_id | date      id | first_name
1  | 1           | ...  ---> 1  | Asha
3  | 2           | ...  ---> 2  | Ben

The basic INNER JOIN

sql
SELECT o.id AS order_id, o.order_date, c.first_name, c.last_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
ORDER BY o.id
LIMIT 5;
result
+----------+------------+------------+-----------+
| order_id | order_date | first_name | last_name |
+----------+------------+------------+-----------+
|        1 | 2024-01-05 | Asha       | Verma     |
|        2 | 2024-02-14 | Asha       | Verma     |
|        3 | 2024-01-20 | Ben        | Carter    |
|        4 | 2024-02-02 | Chloe      | Dubois    |
|        5 | 2024-02-11 | Daniel     | Kim       |
+----------+------------+------------+-----------+

Read it step by step:

  1. FROM orders AS o starts with the orders table, nicknamed o.
  2. INNER JOIN customers AS c adds the customers table, nicknamed c.
  3. ON o.customer_id = c.id says how rows pair up: an order matches the customer whose id equals its customer_id.
  4. The select list can now use columns from both tables.

INNER is optional; plain JOIN means the same thing.

Always qualify columns

Both tables have a column named id. If you write just id, MySQL cannot decide which you mean:

sql
SELECT id, first_name FROM orders o JOIN customers c ON o.customer_id = c.id;
result
ERROR 1052 (23000): Column 'id' in field list is ambiguous

Put the alias in front: o.id or c.id. It is good practice to qualify every column in a join, even the unambiguous ones, so a reader can see which table each comes from.

What "inner" means

An inner join keeps a pair of rows only if the condition matches. Customers 9 and 12 never ordered, so they have no partner and do not appear. Let us count to check:

sql
SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT c.id) AS customers_with_orders
FROM customers c JOIN orders o ON o.customer_id = c.id;
result
+-------------+-----------------------+
| joined_rows | customers_with_orders |
+-------------+-----------------------+
|          20 |                    10 |
+-------------+-----------------------+

There are 20 orders, so the join returns 20 rows, but only ten of the twelve customers appear. If you need customers who have no orders, use LEFT JOIN (next lesson).

Also notice that one customer can appear many times, once per order. A join produces one result row per matching pair.

Adding a WHERE clause

Filter the joined rows as usual. All orders by customers in India:

sql
SELECT o.id, c.first_name, c.country, o.status
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'India'
ORDER BY o.id;
result
+----+------------+---------+-----------+
| id | first_name | country | status    |
+----+------------+---------+-----------+
|  1 | Asha       | India   | delivered |
|  2 | Asha       | India   | delivered |
|  7 | Farid      | India   | delivered |
|  8 | Farid      | India   | shipped   |
| 11 | Asha       | India   | delivered |
| 15 | Kavya      | India   | delivered |
| 19 | Farid      | India   | pending   |
| 20 | Asha       | India   | shipped   |
+----+------------+---------+-----------+

Joining more than one table

Each extra table adds a JOIN ... ON. To see what is inside order 1, connect order_items to orders and products:

sql
SELECT o.id AS order_id, p.name, oi.quantity, oi.unit_price, oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 11;
result
+----------+-----------------------------+----------+------------+------------+
| order_id | name                        | quantity | unit_price | line_total |
+----------+-----------------------------+----------+------------+------------+
|       11 | SQL Basics Handbook         |        1 |      29.95 |      29.95 |
|       11 | Web Design Patterns         |        1 |      34.50 |      34.50 |
|       11 | Data Structures Illustrated |        1 |      45.00 |      45.00 |
+----------+-----------------------------+----------+------------+------------+

A path of keys runs through the whole shop: customers.id ← orders.customer_id, orders.id ← order_items.order_id and products.id ← order_items.product_id. With a diagram of the tables in mind, you can write any join by following that path.

Joining and grouping

Joins combine well with GROUP BY. Revenue per customer, ignoring cancelled orders:

sql
SELECT c.id, CONCAT(c.first_name, ' ', c.last_name) AS customer, SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.first_name, c.last_name
ORDER BY revenue DESC
LIMIT 5;
result
+----+--------------+---------+
| id | customer     | revenue |
+----+--------------+---------+
|  1 | Asha Verma   |  502.58 |
| 10 | Jack Miller  |  351.47 |
|  3 | Chloe Dubois |  274.99 |
|  6 | Farid Khan   |  245.96 |
|  4 | Daniel Kim   |  192.84 |
+----+--------------+---------+

Revenue per product category needs the category name from another table:

sql
SELECT cat.name AS category, SUM(oi.quantity) AS units, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.id, cat.name
ORDER BY revenue DESC;
result
+-------------+-------+---------+
| category    | units | revenue |
+-------------+-------+---------+
| Electronics |    15 | 1149.42 |
| Books       |    10 |  338.70 |
| Home        |    11 |  282.25 |
| Sports      |    14 |  229.45 |
| Toys        |     1 |   49.99 |
+-------------+-------+---------+

USING and the old comma style

When the join columns share a name you can write USING (column) instead of ON:

sql
SELECT p.name, cat.name AS category FROM products p JOIN categories cat ON cat.id = p.category_id LIMIT 3;
result
+---------------------+-------------+
| name                | category    |
+---------------------+-------------+
| Wireless Mouse      | Electronics |
| Mechanical Keyboard | Electronics |
| USB-C Hub           | Electronics |
+---------------------+-------------+

In the sample database the names differ (category_id against id), so ON is needed. You may also see old code that lists tables with commas and puts the condition in WHERE: FROM orders o, customers c WHERE o.customer_id = c.id. It works, but forgetting the condition silently multiplies rows. The JOIN ... ON form keeps the relationship next to the table and is the modern standard.

Common mistakes

  • Ambiguous column names, fixed by qualifying with the alias.
  • A missing or wrong ON condition, giving too many rows. Check the row count against what you expect.
  • Joining on the wrong columns, such as o.id = c.id. The query runs and returns nonsense.
  • Expecting unmatched rows to appear with an inner join.
  • Summing after a join that duplicates rows, which inflates totals. Be clear about which side repeats.

Exercise

Write a query that lists each order line for order 12 with the product name, the category name, quantity and line total. Then list the three customers with the most delivered orders, showing the customer's name and the order count.

Show one possible answer
sql
SELECT p.name AS product, cat.name AS category, oi.quantity, oi.quantity * oi.unit_price AS line_total
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE oi.order_id = 12;

SELECT CONCAT(c.first_name, ' ', c.last_name) AS customer, COUNT(*) AS delivered_orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
GROUP BY c.id, c.first_name, c.last_name
ORDER BY delivered_orders DESC, customer
LIMIT 3;
result
+-----------------------------+-------------+----------+------------+
| product                     | category    | quantity | line_total |
+-----------------------------+-------------+----------+------------+
| Noise-Cancelling Headphones | Electronics |        1 |     199.99 |
| Wireless Mouse              | Electronics |        2 |      49.98 |
+-----------------------------+-------------+----------+------------+
+------------+------------------+
| customer   | delivered_orders |
+------------+------------------+
| Asha Verma |                3 |
| Daniel Kim |                2 |
| Ben Carter |                1 |
+------------+------------------+

Key points

  • A join combines rows from two tables where the ON condition is true. Usually it matches a foreign key to a primary key.
  • INNER JOIN keeps only rows that have a match on both sides. Rows without a match disappear from the result.
  • Use short table aliases and qualify columns (o.id, c.id) so names are never ambiguous.
  • You can chain joins across many tables. Each extra table is another JOIN ... ON.

Check yourself

1. In orders INNER JOIN customers ON orders.customer_id = customers.id, which orders appear?
2. Why does SELECT id FROM orders o JOIN customers c ON o.customer_id = c.id fail?
3. What happens to a customer who has never placed an order in an INNER JOIN of customers and orders?
4. Which clause states how two tables are related in a join?