Most “wrong results” in SQL come from a few repeated mistakes. Each one is shown below with a fix.
CREATE TABLE staff (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
department VARCHAR(20) NOT NULL,
salary INT NOT NULL,
manager_id INT NULL,
left_on DATE NULL
);
INSERT INTO staff (name, department, salary, manager_id, left_on) VALUES
('Asha', 'engineering', 120000, NULL, NULL),
('Ben', 'engineering', 90000, 1, NULL),
('Chitra', 'sales', 70000, 1, '2026-03-31'),
('Dev', 'sales', 65000, 3, NULL),
('Esha', 'support', 50000, 1, NULL);1. = NULL instead of IS NULL
SELECT name FROM staff WHERE left_on = NULL;
SELECT name FROM staff WHERE left_on IS NULL ORDER BY id;Empty set
+------+
| name |
+------+
| Asha |
| Ben |
| Dev |
| Esha |
+------+
4 rows in set2. OR without brackets
SELECT name, department, salary FROM staff WHERE department = 'engineering' OR department = 'sales' AND salary > 80000 ORDER BY id;
SELECT name, department, salary FROM staff WHERE (department = 'engineering' OR department = 'sales') AND salary > 80000 ORDER BY id;+------+-------------+--------+
| name | department | salary |
+------+-------------+--------+
| Asha | engineering | 120000 |
| Ben | engineering | 90000 |
+------+-------------+--------+
2 rows in set
+------+-------------+--------+
| name | department | salary |
+------+-------------+--------+
| Asha | engineering | 120000 |
| Ben | engineering | 90000 |
+------+-------------+--------+
2 rows in setBen (engineering, 90000) and Asha qualify in both. In the first query, Chitra and Dev would be included only if they earned more than 80000, and AND was applied only to sales. Be explicit.
3. NOT IN with NULL in the list
SELECT name FROM staff WHERE id NOT IN (SELECT manager_id FROM staff);
SELECT name FROM staff s WHERE NOT EXISTS (SELECT 1 FROM staff m WHERE m.manager_id = s.id) ORDER BY id;Empty set
+------+
| name |
+------+
| Ben |
| Dev |
| Esha |
+------+
3 rows in setThe first query returns nothing because manager_id contains NULL. The second correctly lists the people who manage nobody.
4. A function on the column
SELECT name FROM staff WHERE YEAR(left_on) = 2026;
SELECT name FROM staff WHERE left_on >= '2026-01-01' AND left_on < '2027-01-01';+--------+
| name |
+--------+
| Chitra |
+--------+
1 row in set
+--------+
| name |
+--------+
| Chitra |
+--------+
1 row in setSame answer, but the second form can use an index (Sargable Conditions).
5. Comparing a number with text
SELECT name FROM staff WHERE department = 0 ORDER BY id;+--------+
| name |
+--------+
| Asha |
| Ben |
| Chitra |
| Dev |
| Esha |
+--------+
5 rows in setEvery row matches! department = 0 converts the text to a number ('engineering' becomes 0), and all of them equal zero. That is the type-conversion trap (Type Conversion). Compare text with text.
6. Searching with BETWEEN on DATETIME
Covered in BETWEEN. Use >= start AND < next_start.
7. LIKE with a leading wildcard on a big table
LIKE '%son' reads every row. Consider full-text search (Full-Text Search).
8. Forgetting WHERE on UPDATE or DELETE
UPDATE staff SET salary = 0;
SELECT name, salary FROM staff ORDER BY id LIMIT 2;+------+--------+
| name | salary |
+------+--------+
| Asha | 0 |
| Ben | 0 |
+------+--------+
2 rows in setEvery salary is now 0. Without a WHERE, UPDATE and DELETE hit all rows. Protect yourself:
- Run the matching
SELECTfirst. - Run changes inside a transaction and check the row count before
COMMIT(Transactions). - In the
mysqlclient, start it with--safe-updates(also-U), which refusesUPDATEandDELETEthat have no key in theWHEREor aLIMIT.
ROLLBACK;(The rollback does nothing here, because autocommit had already saved the statement. In a real job, you would have started a transaction first.)
9. WHERE vs HAVING
WHERE filters rows before grouping. HAVING filters groups after. A condition on an aggregate (COUNT(*) > 1) belongs in HAVING (HAVING).
10. Trusting case or space
WHERE name = 'asha' matches Asha by default. Trailing spaces are significant in the default collation. Values with hidden spaces from imports do not match: use TRIM() when cleaning data, not in every query (Importing CSV Safely).
A debugging method
- Remove all conditions. Is the data what you think?
- Add them back one at a time, checking the row count after each.
- Print the intermediate values:
SELECT name, department, salary, department = 'sales' AS is_sales .... - Read the query in execution order: FROM, WHERE, GROUP BY, HAVING, SELECT (Order of Evaluation).
A habit that saves jobs
SELECT with the same WHERE and read the rows. If they are right, change SELECT * to DELETE or UPDATE.