Most SQL bugs are not syntax errors. The query runs and returns wrong results, and nobody notices. This lesson shows ten classic bugs, with the real output of the wrong query, the explanation, and the fix. Try to spot the problem before you read the explanation.
CREATE TABLE people (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, phone VARCHAR(15) NULL, city VARCHAR(20) NULL, age INT);
INSERT INTO people VALUES (1, 'Maya', '9000000001', 'Pune', 31), (2, 'Ben', NULL, 'Delhi', 28), (3, 'Isha', '9000000003', NULL, 31), (4, 'Kabir', NULL, NULL, NULL), (5, 'Zoya', '9000000005', 'Pune', 25);
CREATE TABLE orders (id INT PRIMARY KEY, person_id INT NOT NULL, total DECIMAL(8,2) NOT NULL, placed_on DATETIME NOT NULL);
INSERT INTO orders VALUES (1, 1, 100.00, '2026-03-30 10:00:00'), (2, 1, 50.00, '2026-03-31 18:30:00'), (3, 2, 75.00, '2026-03-15 09:00:00'), (4, 5, 20.00, '2026-04-01 08:00:00');Query OK, 0 rows affected
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0Bug 1: comparing with NULL
“List the people with no phone.”
SELECT name FROM people WHERE phone = NULL;Empty setNobody is returned, although Ben and Kabir have no phone. = NULL is never true, because a comparison with an unknown value is unknown. Use IS NULL (NULL):
SELECT name FROM people WHERE phone IS NULL ORDER BY name;+-------+
| name |
+-------+
| Ben |
| Kabir |
+-------+
2 rows in setBug 2: COUNT(*) is not COUNT(column)
“How many people have a city?”
SELECT COUNT(*) AS wrong_count, COUNT(city) AS people_with_city FROM people;+-------------+------------------+
| wrong_count | people_with_city |
+-------------+------------------+
| 5 | 3 |
+-------------+------------------+
1 row in setCOUNT(*) counts rows (5). COUNT(city) counts only rows where city is not NULL (3). Pick the one that answers the question (Aggregates).
Bug 3: NOT IN and NULL
“Which people have never referred anyone?” A referrals table stores who referred whom. Some referrals have no known referrer (NULL):
CREATE TABLE referrals (id INT PRIMARY KEY, referrer_id INT NULL, referred_name VARCHAR(20) NOT NULL);
INSERT INTO referrals VALUES (1, 1, 'Zoya'), (2, NULL, 'Walk-in customer');
SELECT name FROM people WHERE id NOT IN (SELECT referrer_id FROM referrals);Empty setThe result is empty, but people 2 to 5 have never referred anyone. The subquery returns 1, NULL, and id NOT IN (1, NULL) is never true: “not equal to NULL” is unknown. NOT IN with a subquery that can return NULL returns nothing. Use NOT EXISTS, or remove the NULLs (Subqueries):
SELECT p.name FROM people p WHERE NOT EXISTS (SELECT 1 FROM referrals r WHERE r.referrer_id = p.id) ORDER BY p.name;+-------+
| name |
+-------+
| Ben |
| Isha |
| Kabir |
| Zoya |
+-------+
4 rows in setBug 4: an aggregate in WHERE
“People with more than one order.”
SELECT person_id FROM orders WHERE COUNT(*) > 1 GROUP BY person_id;ERROR 1111 (HY000): Invalid use of group functionWHERE runs before the grouping, so it cannot use COUNT(*). Conditions on aggregates go in HAVING:
SELECT person_id, COUNT(*) AS orders FROM orders GROUP BY person_id HAVING COUNT(*) > 1;+-----------+--------+
| person_id | orders |
+-----------+--------+
| 1 | 2 |
+-----------+--------+
1 row in setBug 5: a column that is not grouped
“Show the oldest person’s name per city.”
SELECT city, name, MAX(age) FROM people GROUP BY city;ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'learn.people.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byMySQL 8 runs with ONLY_FULL_GROUP_BY: every selected column must be grouped or aggregated. name is neither, and which name would belong to the maximum age is undefined. (Old MySQL versions silently returned an arbitrary name.) The right answer needs a different approach, such as a window function (Top N per Group):
SELECT city, name, age FROM (SELECT city, name, age, ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC, name) AS r FROM people WHERE city IS NOT NULL) x WHERE r = 1 ORDER BY city;+-------+------+------+
| city | name | age |
+-------+------+------+
| Delhi | Ben | 28 |
| Pune | Maya | 31 |
+-------+------+------+
2 rows in setBug 6: a WHERE that turns a LEFT JOIN into an inner join
“All people, and the number of their orders above 60.”
SELECT p.name, COUNT(o.id) AS big_orders FROM people p LEFT JOIN orders o ON o.person_id = p.id WHERE o.total > 60 GROUP BY p.id, p.name ORDER BY p.name;+------+------------+
| name | big_orders |
+------+------------+
| Ben | 1 |
| Maya | 1 |
+------+------------+
2 rows in setIsha, Kabir and Zoya are missing. For them, o.total is NULL (no match), and NULL > 60 is not true, so WHERE removes the rows that the LEFT JOIN had kept. Move the condition into ON:
SELECT p.name, COUNT(o.id) AS big_orders FROM people p LEFT JOIN orders o ON o.person_id = p.id AND o.total > 60 GROUP BY p.id, p.name ORDER BY p.name;+-------+------------+
| name | big_orders |
+-------+------------+
| Ben | 1 |
| Isha | 0 |
| Kabir | 0 |
| Maya | 1 |
| Zoya | 0 |
+-------+------------+
5 rows in setBug 7: joins that multiply rows
“Total of each person’s orders, with their phone and a label.” Joining a second one-to-many table repeats the rows:
CREATE TABLE tags (person_id INT NOT NULL, tag VARCHAR(10) NOT NULL);
INSERT INTO tags VALUES (1, 'vip'), (1, 'beta'), (1, 'early');
SELECT p.name, SUM(o.total) AS wrong_total
FROM people p JOIN orders o ON o.person_id = p.id JOIN tags t ON t.person_id = p.id
GROUP BY p.id, p.name;+------+-------------+
| name | wrong_total |
+------+-------------+
| Maya | 450.00 |
+------+-------------+
1 row in setMaya’s orders total 150, but the result says 450: each of her 2 orders appears once per tag (3 tags), tripling the sum. Aggregate each side before joining, or use a subquery:
SELECT p.name, (SELECT SUM(o.total) FROM orders o WHERE o.person_id = p.id) AS total, (SELECT GROUP_CONCAT(t.tag ORDER BY t.tag) FROM tags t WHERE t.person_id = p.id) AS tags
FROM people p WHERE p.id = 1;+------+--------+----------------+
| name | total | tags |
+------+--------+----------------+
| Maya | 150.00 | beta,early,vip |
+------+--------+----------------+
1 row in setBug 8: numbers stored as text
CREATE TABLE versions (v VARCHAR(10));
INSERT INTO versions VALUES ('9'), ('10'), ('100'), ('2');
SELECT v FROM versions ORDER BY v;+------+
| v |
+------+
| 10 |
| 100 |
| 2 |
| 9 |
+------+
4 rows in setAs text, '10' sorts before '2' (character by character). If the values are numbers, store them as numbers. If you cannot change the column, convert in the query:
SELECT v FROM versions ORDER BY CAST(v AS UNSIGNED);+------+
| v |
+------+
| 2 |
| 9 |
| 10 |
| 100 |
+------+
4 rows in setBug 9: BETWEEN and a DATETIME
“Orders placed in March.”
SELECT id, placed_on FROM orders WHERE placed_on BETWEEN '2026-03-01' AND '2026-03-31' ORDER BY id;+----+---------------------+
| id | placed_on |
+----+---------------------+
| 1 | 2026-03-30 10:00:00 |
| 3 | 2026-03-15 09:00:00 |
+----+---------------------+
2 rows in setOrder 2 (31 March at 18:30) is missing. '2026-03-31' means midnight at the start of that day, so everything later on the 31st is outside the range. Use a half-open range:
SELECT id, placed_on FROM orders WHERE placed_on >= '2026-03-01' AND placed_on < '2026-04-01' ORDER BY id;+----+---------------------+
| id | placed_on |
+----+---------------------+
| 1 | 2026-03-30 10:00:00 |
| 2 | 2026-03-31 18:30:00 |
| 3 | 2026-03-15 09:00:00 |
+----+---------------------+
3 rows in setBug 10: case-insensitive comparisons
CREATE TABLE logins (username VARCHAR(20) NOT NULL, UNIQUE KEY uq_username (username));
INSERT INTO logins VALUES ('Maya');
INSERT INTO logins VALUES ('maya');ERROR 1062 (23000): Duplicate entry 'maya' for key 'logins.uq_username'The default collation utf8mb4_0900_ai_ci is case-insensitive and accent-insensitive, so 'Maya' and 'maya' are the same value, and the unique index refuses the second. That is often wanted for usernames and emails. If you need a case-sensitive comparison for one query, say so (Character Sets and Collations):
SELECT 'Maya' = 'maya' AS ci_equal, 'Maya' COLLATE utf8mb4_0900_as_cs = 'maya' AS cs_equal;+----------+----------+
| ci_equal | cs_equal |
+----------+----------+
| 1 | 0 |
+----------+----------+
1 row in setA debugging routine
- Read the error message to the end. It names the line and the problem.
- Run the pieces: the subquery alone, the join without the
WHERE, theGROUP BYwithout theHAVING. - Check the row count after every join. If it grew unexpectedly, a join multiplies rows.
- Look for
NULL: in comparisons,NOT IN, joins and aggregates. - Use
EXPLAINwhen the result is right but slow (EXPLAIN). - Keep a tiny test table with the awkward values (NULLs, ties, empty groups, boundary dates), and run every important query on it.