Common MySQL JOIN Mistakes: Duplicates, Missing Rows

Most join bugs fall into a few families. This lesson lists them, with a small example for each.

query.sql

SQL

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(20));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, total INT);
CREATE TABLE addresses (id INT PRIMARY KEY, customer_id INT, type VARCHAR(10), line VARCHAR(20));
INSERT INTO customers VALUES (1, 'Maya', 'Pune'), (2, 'Rohan', 'Delhi'), (3, 'Sana', NULL);
INSERT INTO orders VALUES (1, 1, 100), (2, 1, 50), (3, 2, 70), (4, NULL, 25);
INSERT INTO addresses VALUES (1, 1, 'home', 'Pune East'), (2, 1, 'work', 'Pune West'), (3, 2, 'home', 'Delhi North');

1. Unexpected duplicate rows

Maya has two addresses, so joining addresses repeats her orders:

query.sql

SQL

SELECT c.name, o.id AS order_id, a.line
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN addresses a ON a.customer_id = c.id
ORDER BY c.id, o.id, a.id;

Output

OUTPUT

+-------+----------+-------------+
| name  | order_id | line        |
+-------+----------+-------------+
| Maya  |        1 | Pune East   |
| Maya  |        1 | Pune West   |
| Maya  |        2 | Pune East   |
| Maya  |        2 | Pune West   |
| Rohan |        3 | Delhi North |
+-------+----------+-------------+
5 rows in set

Maya’s two orders each appear twice (once per address): four rows. A join multiplies rows when the matching is one-to-many on both sides. Fix it by restricting the second join to one address (AND a.type = 'home'), or by joining a one-row-per-customer subquery.

2. Rows that disappear

An INNER JOIN drops rows with no partner. Sana has no orders, so she is gone:

query.sql

SQL

1
2

SELECT c.name, COUNT(o.id) AS orders FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY c.id;
SELECT c.name, COUNT(o.id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY c.id;

Output

OUTPUT

+-------+--------+
| name  | orders |
+-------+--------+
| Maya  |      2 |
| Rohan |      1 |
+-------+--------+
2 rows in set

+-------+--------+
| name  | orders |
+-------+--------+
| Maya  |      2 |
| Rohan |      1 |
| Sana  |      0 |
+-------+--------+
3 rows in set

3. A hidden inner join (WHERE on the right table)

query.sql

SQL

1
2

SELECT c.name, o.total FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 60 ORDER BY c.id;
SELECT c.name, o.total FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 60 ORDER BY c.id, o.id;

Output

OUTPUT

+-------+-------+
| name  | total |
+-------+-------+
| Maya  |   100 |
| Rohan |    70 |
+-------+-------+
2 rows in set

+-------+-------+
| name  | total |
+-------+-------+
| Maya  |   100 |
| Rohan |    70 |
| Sana  |  NULL |
+-------+-------+
3 rows in set

The first query drops Sana, because her o.total is NULL. The second keeps her (ON vs WHERE).

4. Wrong totals after a join

query.sql

SQL

1
2

SELECT SUM(o.total) AS inflated FROM customers c JOIN orders o ON o.customer_id = c.id JOIN addresses a ON a.customer_id = c.id;
SELECT SUM(total) AS correct FROM orders WHERE customer_id IS NOT NULL;

Output

OUTPUT

+----------+
| inflated |
+----------+
|      370 |
+----------+
1 row in set

+---------+
| correct |
+---------+
|     220 |
+---------+
1 row in set

The first query adds Maya’s orders twice because of the address join (Aggregate Pitfalls).

5. NULL join keys never match

An order with customer_id = NULL matches no customer, even in a LEFT JOIN from customers: NULL = anything is not true.

query.sql

SQL

SELECT o.id, o.customer_id, c.name FROM orders o LEFT JOIN customers c ON c.id = o.customer_id ORDER BY o.id;

Output

OUTPUT

+----+-------------+-------+
| id | customer_id | name  |
+----+-------------+-------+
|  1 |           1 | Maya  |
|  2 |           1 | Maya  |
|  3 |           2 | Rohan |
|  4 |        NULL | NULL  |
+----+-------------+-------+
4 rows in set

Order 4 shows NULL. If you want NULLs to match each other, use the NULL-safe operator <=> in the condition. That is rare for foreign keys, where NULL means “no relation”.

6. Ambiguous column names

query.sql

SQL

SELECT id, name FROM customers c JOIN orders o ON o.customer_id = c.id;

Output

OUTPUT

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

Both tables have id. Always prefix: c.id, o.id.

7. Joining on the wrong columns

query.sql

SQL

SELECT c.name, o.id FROM customers c JOIN orders o ON o.id = c.id ORDER BY c.id;

Output

OUTPUT

+-------+----+
| name  | id |
+-------+----+
| Maya  |  1 |
| Rohan |  2 |
| Sana  |  3 |
+-------+----+
3 rows in set

It runs and returns rows: order 1 with customer 1, order 2 with customer 2. It is wrong, because the order’s own id was compared with the customer’s id. Typos in the ON condition do not cause errors when the types fit. Check the keys, and test with a few rows you can verify by hand.

8. Mixing up the join type and the table order

A LEFT JOIN B keeps all of A. Put the table whose rows you need to keep on the left (LEFT JOIN).

9. Joining on non-key columns

A join on city or name (non-unique) multiplies rows in surprising ways:

query.sql

SQL

SELECT COUNT(*) AS rows_returned FROM customers a JOIN customers b ON a.city = b.city;

Output

OUTPUT

+---------------+
| rows_returned |
+---------------+
|             2 |
+---------------+
1 row in set

Each customer is matched with every customer of the same city, including themselves (Sana’s city is NULL, so she matches nobody). With real data, where many customers share a city, the row count explodes. Join on unique keys, or accept and handle the multiplication.

10. Joins in UPDATE and DELETE

A join before UPDATE or DELETE can touch more rows than you expect. Preview with SELECT first (UPDATE with JOIN).

A routine for every new join

Write down the grain of the result: “one row per ___”.
Check how many rows each table has, and the join cardinality (one-to-one, one-to-many, many-to-many).
Run the query with COUNT(*), and compare with the expected number.
Test with a row that has no match and a row that has several.
Compare totals with a simple query on one table.

lightbulb

Check both sides

For every join ask two questions: “Can a row on the left match more than one row on the right?” and “Can it match none?” The answers tell you whether to expect duplicates and whether to use a LEFT JOIN.