INNER JOIN returns only the rows where the join condition is true in both tables. A row without a partner on the other side is left out. The word INNER is optional: JOIN means INNER JOIN in MySQL.
query.sql
SQL
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
ordered_on DATE NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO customers (name, city) VALUES
('Maya Shah', 'Pune'),
('Rohan Verma', 'Delhi'),
('Sana Ali', 'Pune'),
('Tara Nair', 'Kochi'),
('Uday Singh', 'Delhi');
INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
(1, '2026-05-02', 680.00, 'delivered'),
(1, '2026-05-20', 1299.00, 'delivered'),
(2, '2026-05-21', 145.00, 'shipped'),
(3, '2026-06-01', 450.00, 'delivered'),
(3, '2026-06-05', 80.00, 'cancelled'),
(3, '2026-06-18', 999.00, 'shipped'),
(2, '2026-06-20', 220.00, 'pending');
query.sql
SQL
SELECT c.name, c.city, o.id AS order_id, o.ordered_on, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.ordered_on;
Output
OUTPUT
+-------------+-------+----------+------------+---------+
| name | city | order_id | ordered_on | total |
+-------------+-------+----------+------------+---------+
| Maya Shah | Pune | 1 | 2026-05-02 | 680.00 |
| Maya Shah | Pune | 2 | 2026-05-20 | 1299.00 |
| Rohan Verma | Delhi | 3 | 2026-05-21 | 145.00 |
| Rohan Verma | Delhi | 7 | 2026-06-20 | 220.00 |
| Sana Ali | Pune | 4 | 2026-06-01 | 450.00 |
| Sana Ali | Pune | 5 | 2026-06-05 | 80.00 |
| Sana Ali | Pune | 6 | 2026-06-18 | 999.00 |
+-------------+-------+----------+------------+---------+
7 rows in setTara and Uday do not appear: they have no orders. Seven orders matched, so seven rows came out.
Filtering joined rows
Use WHERE for conditions on any of the joined tables:
query.sql
SQL
SELECT c.name, o.total, o.status
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Pune' AND o.status = 'delivered'
ORDER BY o.total DESC;
Output
OUTPUT
+-----------+---------+-----------+
| name | total | status |
+-----------+---------+-----------+
| Maya Shah | 1299.00 | delivered |
| Maya Shah | 680.00 | delivered |
| Sana Ali | 450.00 | delivered |
+-----------+---------+-----------+
3 rows in setFor an inner join, a condition in ON and the same condition in WHERE give the same result. For an outer join they do not (ON vs WHERE).
One row on the left, many on the right
A customer with 3 orders appears 3 times. That is correct: one result row per matching pair. Use GROUP BY if you want one row per customer:
query.sql
SQL
SELECT c.name, COUNT(*) AS orders, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC;
Output
OUTPUT
+-------------+--------+---------+
| name | orders | spent |
+-------------+--------+---------+
| Maya Shah | 2 | 1979.00 |
| Sana Ali | 3 | 1529.00 |
| Rohan Verma | 2 | 365.00 |
+-------------+--------+---------+
3 rows in setJoining on several columns
A join condition can have several parts. For example, matching a price list by product and date:
query.sql
SQL
CREATE TABLE price_list (product VARCHAR(10), valid_from DATE, price INT);
CREATE TABLE sales (id INT AUTO_INCREMENT PRIMARY KEY, product VARCHAR(10), sold_on DATE);
INSERT INTO price_list VALUES ('pen', '2026-01-01', 10), ('pen', '2026-06-01', 12), ('book', '2026-01-01', 100);
INSERT INTO sales (product, sold_on) VALUES ('pen', '2026-02-10'), ('pen', '2026-06-15'), ('book', '2026-03-01');
SELECT s.id, s.product, s.sold_on, p.price
FROM sales s
JOIN price_list p ON p.product = s.product
AND p.valid_from = (SELECT MAX(valid_from) FROM price_list WHERE product = s.product AND valid_from <= s.sold_on)
ORDER BY s.id;
Output
OUTPUT
+----+---------+------------+-------+
| id | product | sold_on | price |
+----+---------+------------+-------+
| 1 | pen | 2026-02-10 | 10 |
| 2 | pen | 2026-06-15 | 12 |
| 3 | book | 2026-03-01 | 100 |
+----+---------+------------+-------+
3 rows in setEach sale is matched with the price that was valid on the day of the sale.
Non-equal joins
The condition does not have to be =. You can join on a range:
query.sql
SQL
CREATE TABLE grade_bands (grade CHAR(1), min_score INT, max_score INT);
INSERT INTO grade_bands VALUES ('A', 80, 100), ('B', 60, 79), ('C', 0, 59);
CREATE TABLE exam_results (student VARCHAR(10), score INT);
INSERT INTO exam_results VALUES ('Asha', 91), ('Ben', 67), ('Chitra', 45);
SELECT e.student, e.score, g.grade
FROM exam_results e
JOIN grade_bands g ON e.score BETWEEN g.min_score AND g.max_score
ORDER BY e.student;
Output
OUTPUT
+---------+-------+-------+
| student | score | grade |
+---------+-------+-------+
| Asha | 91 | A |
| Ben | 67 | B |
| Chitra | 45 | C |
+---------+-------+-------+
3 rows in setColumns with the same name
If both tables have a column called id or name, SELECT * returns both, and your application may confuse them. Select the columns explicitly and give aliases:
query.sql
SQL
SELECT c.id AS customer_id, c.name, o.id AS order_id FROM customers c JOIN orders o ON o.customer_id = c.id ORDER BY o.id LIMIT 3;
Output
OUTPUT
+-------------+-------------+----------+
| customer_id | name | order_id |
+-------------+-------------+----------+
| 1 | Maya Shah | 1 |
| 1 | Maya Shah | 2 |
| 2 | Rohan Verma | 3 |
+-------------+-------------+----------+
3 rows in setCommon mistakes
- Forgetting the condition (
JOIN ... ON 1 = 1, or a comma join withoutWHERE). The result has every pair of rows. - Joining on the wrong columns.
ON c.id = o.idcompiles but is nonsense. - Joining on columns of different types. It prevents index use and may cause strange matches.
- Expecting one row per customer and getting several.
- **Using
SELECT *** and getting duplicate column names.
lightbulb
Check the row count
After writing a join, compare
COUNT(*) of the joined result with what you expect. Too many rows means a one-to-many match you did not account for.