INNER JOIN

The orders table says that order 110 was placed by customer 8. That is correct, but not very helpful: who is customer 8, and where do they live? The answer is in a different table, customers. To see both together you need a join.

Joins are the heart of relational databases. In this lesson you will learn INNER JOIN, the most common kind. You will join two tables, then three and four, and combine joins with GROUP BY to build a real sales report.

Why the data is split into tables

The shop could store the customer's name and city inside every order row. But then Aarav's details would be copied into all three of his orders, and if he moved from Rohtak to Delhi, someone would have to change every copy. Miss one, and the data contradicts itself.

So the database stores each fact once: customer details in customers, and only the customer_id in orders. A join puts the pieces back together when you query them. (Why this design is good is covered in Normalisation & Table Design.)

Joining two tables

An INNER JOIN combines each row of one table with the matching rows of another. You say what "matching" means in the ON condition:

SELECT o.order_id, o.order_date, c.first_name, c.city
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-04-01';

order_id | order_date | first_name | city
---------+------------+------------+----------
110      | 2025-04-01 | Sneha      | Delhi
111      | 2025-04-10 | Arjun      | Hyderabad
112      | 2025-04-18 | Isha       | Gurugram
113      | 2025-05-02 | Rohan      | Mumbai
114      | 2025-05-11 | Farhan     | Lucknow
115      | 2025-05-20 | Aarav      | Rohtak
(6 rows)

Read the FROM part as: "take the orders, and for each one find the customer whose customer_id equals the order's customer_id". Each result row holds columns from both tables. The WHERE, ORDER BY and other clauses work exactly as before, on the joined rows.

The letters o and c are table aliases (see Aliases). o.customer_id means the customer_id column of orders, and c.customer_id the one in customers.

Only matching rows are kept

Inner means a row appears in the result only when it has a match on both sides. Divya (customer 12) has never placed an order, so no order row matches her, and she never appears in an inner join between customers and orders. Likewise, the Graphics Tablet has never been ordered, so it is missing from any inner join with order_items.

That is often what you want ("show me orders with their customers"). When you also need the rows without a match, such as "customers who have never ordered", use a LEFT JOIN instead.

JOIN on its own means INNER JOIN, so most people write the shorter form. The examples below do too.

Always say which table a column comes from

Both tables have a customer_id column. If you write it without a prefix, MySQL does not know which one you mean:

SELECT order_id, customer_id, first_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

ERROR 1052 (23000): Column 'customer_id' in field list is ambiguous

Columns that exist in only one table, like first_name, work without a prefix, but it is good practice to prefix every column in a join. Anyone reading the query can then see at once where each value comes from.

Joining three or four tables

You can keep adding JOIN ... ON ... lines. Each one brings in another table, linked to a table already in the query. To answer "what did Priya buy?", you travel from customers to orders to order_items to products:

SELECT o.order_id, o.order_date, p.product_name, oi.quantity, oi.unit_price
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE c.first_name = 'Priya'
ORDER BY o.order_date;

order_id | order_date | product_name         | quantity | unit_price
---------+------------+----------------------+----------+-----------
102      | 2025-01-12 | 27-inch Monitor      | 1        | 15999
109      | 2025-03-22 | Python Basics (Book) | 2        | 450
(2 rows)

Priya placed two orders: a monitor in January and two copies of the Python book in March. Look at the ON conditions: each one follows a foreign key from The Practice Database. If you get stuck on a join, draw the tables and the arrows between them first.

Joins with GROUP BY

Joins become really powerful when you combine them with grouping. Earlier, the best-selling products report could only show IDs. With a join it shows names:

SELECT p.product_name,
       SUM(oi.quantity) AS units,
       SUM(oi.quantity * oi.unit_price) AS sales
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY sales DESC
LIMIT 5;

product_name                | units | sales
----------------------------+-------+------
27-inch Monitor             | 3     | 47997
Noise-Cancelling Headphones | 2     | 17998
1TB External SSD            | 2     | 12998
Mechanical Keyboard         | 2     | 6998
USB-C Hub                   | 3     | 5697
(5 rows)

And here are the three biggest-spending customers, counting only orders that were not cancelled:

SELECT c.first_name, c.last_name,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 3;

first_name | last_name | total_spent
-----------+-----------+------------
Sneha      | Gupta     | 32797
Priya      | Verma     | 16899
Aarav      | Sharma    | 14944
(3 rows)

Sneha has spent the most, thanks to her two monitors. Grouping by the primary key c.customer_id keeps two different customers with the same name apart, and listing the name columns in GROUP BY as well keeps the query valid in every database.

Common mistakes

• Leaving column names unqualified. A column that exists in both tables gives error 1052 (ambiguous). Prefix every column with its table alias.
• Joining on the wrong columns. ON o.order_id = c.customer_id runs without any error but matches orders to random customers. Always join a foreign key to the primary key it points to.
• Forgetting that INNER JOIN drops rows. If a report seems to be missing customers or products, they probably have no match. Use a LEFT JOIN when unmatched rows matter.
• Adding up after joining to a "many" table. Joining orders to order items repeats each order once per item. Summing an order-level value after that join counts it several times. Sum item-level values, as above, or group first.

Key points

• INNER JOIN ... ON condition combines rows from two tables where the condition matches.
• Rows without a match on both sides are left out.
• JOIN is short for INNER JOIN.
• Use table aliases and prefix every column to avoid ambiguity.
• Chain several joins to follow foreign keys across many tables.
• Joins work together with WHERE, GROUP BY, HAVING and ORDER BY.

SELECT c.first_name, o.order_id, p.product_name, oi.quantity
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE c.city = 'Rohtak'
ORDER BY o.order_id, oi.item_id;