Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 48 of 49

Ten common SQL mistakes

Ten SQL mistakes that give wrong results or errors without warning, from comparing to NULL to missing join conditions, each shown with real output and the fix.

16 minPracticeUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonRecognise ten classic SQL mistakes by their symptoms, explain why each happens, and write the corrected query.

What you will be able to do

After this lesson you can spot ten mistakes that trip up nearly everyone learning SQL. The worst ones do not fail loudly. They return a result that looks right and is wrong. Each section shows the mistake, the real output on the sample database, and the fix.

1. Comparing to NULL with =

NULL means "unknown", and nothing is equal to an unknown, not even another NULL.

sql
SELECT first_name FROM employees WHERE manager_id = NULL;

No rows come back, although six employees have no manager (the five department heads, plus Ravi the consultant). The fix is IS NULL:

sql
SELECT first_name FROM employees WHERE manager_id IS NULL ORDER BY id;
result
+------------+
| first_name |
+------------+
| Meera      |
| Vikram     |
| Dev        |
| Lata       |
| Pooja      |
| Ravi       |
+------------+

2. COUNT(column) versus COUNT(*)

COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL, so the two can differ:

sql
SELECT COUNT(*) AS all_rows, COUNT(manager_id) AS with_manager, COUNT(department_id) AS with_department
FROM employees;
result
+----------+--------------+-----------------+
| all_rows | with_manager | with_department |
+----------+--------------+-----------------+
|       20 |           14 |              19 |
+----------+--------------+-----------------+

If you wanted the number of employees, COUNT(*) was right. If you counted manager_id you would have said 14 and been wrong.

3. A plain column that is not in GROUP BY

Every column in the select list of a grouped query must either be in GROUP BY or be inside an aggregate. Which department name goes beside a count of employees across all departments? There is no single answer. The standard behaviour and most databases (PostgreSQL, SQL Server, and MySQL with its default settings) give an error for this query, so it is not run here, because SQLite lets it through and returns an arbitrary value, which is even more dangerous:

sql
SELECT department_id, first_name, COUNT(*) FROM employees GROUP BY department_id;

Fix it by grouping on what you display, or aggregate the other column:

sql
SELECT department_id, COUNT(*) AS headcount, MIN(hire_date) AS first_hire
FROM employees
GROUP BY department_id
ORDER BY department_id;
result
+---------------+-----------+------------+
| department_id | headcount | first_hire |
+---------------+-----------+------------+
| NULL          |         1 | 2024-01-08 |
|             1 |         6 | 2018-03-12 |
|             2 |         5 | 2017-11-02 |
|             3 |         3 | 2018-10-15 |
|             4 |         2 | 2016-04-25 |
|             5 |         3 | 2019-09-23 |
+---------------+-----------+------------+

4. Using an aggregate in WHERE

WHERE is applied before the rows are grouped, so it cannot see COUNT or AVG. To filter groups, use HAVING:

sql
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE COUNT(*) > 3
GROUP BY department_id;
result
Parse error near line 1: misuse of aggregate: COUNT()

Fixed:

sql
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3
ORDER BY department_id;
result
+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
|             1 |         6 |
|             2 |         5 |
+---------------+-----------+

5. Forgetting the join condition

Listing two tables with no condition pairs every row with every row, a Cartesian product:

sql
SELECT COUNT(*) AS rows_returned FROM employees, departments;
result
+---------------+
| rows_returned |
+---------------+
|           120 |
+---------------+

That is 20 employees times 6 departments, 120 rows, with each employee paired with every department. The fix is an explicit JOIN ... ON:

sql
SELECT COUNT(*) AS rows_returned
FROM employees e
JOIN departments d ON d.id = e.department_id;
result
+---------------+
| rows_returned |
+---------------+
|            19 |
+---------------+

6. Losing rows with an INNER JOIN

The corrected join above returns 19 rows, not 20, because employee 18 (Ravi) has no department and an inner join keeps only matches. If you need every employee, use a LEFT JOIN:

sql
SELECT e.first_name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
WHERE e.id IN (17, 18)
ORDER BY e.id;
result
+------------+------------+
| first_name | department |
+------------+------------+
| Sana       | Support    |
| Ravi       | NULL       |
+------------+------------+

7. NOT IN with a list that contains NULL

"Which employees do not manage anyone?" The obvious query:

sql
SELECT first_name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

It returns no rows at all, because manager_id contains NULL, and id NOT IN (..., NULL) can never be true. Use NOT EXISTS, which handles this properly:

sql
SELECT e.first_name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id)
ORDER BY e.id;
result
+------------+
| first_name |
+------------+
| Rohan      |
| Isha       |
| Sameer     |
| Tara       |
| Anika      |
| Yash       |
| Omar       |
| Karan      |
| Sana       |
| Ravi       |
| Diya       |
| Aman       |
+------------+

8. Integer division

When both sides are integers, many databases divide as integers and throw the fraction away:

sql
SELECT 7 / 2 AS integer_division, 7 / 2.0 AS decimal_division;
result
+------------------+------------------+
| integer_division | decimal_division |
+------------------+------------------+
|                3 |              3.5 |
+------------------+------------------+

SQLite, PostgreSQL and SQL Server all give 3 for 7 / 2. MySQL's / already returns a decimal (3.5000) and has a separate DIV operator for integer division. The portable habit is to make one side a decimal, as 2.0 does, or to CAST it.

9. Filtering the right table of a LEFT JOIN in WHERE

Suppose you want every department with its employees earning over 100,000, keeping departments that have none. Putting the salary test in WHERE quietly removes departments without a match, because their salary is NULL:

sql
SELECT d.name AS department, e.first_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.salary > 100000
ORDER BY d.name;
result
+-------------+------------+
| department  | first_name |
+-------------+------------+
| Engineering | Meera      |
| Engineering | Arjun      |
| Engineering | Kavya      |
| Finance     | Lata       |
| Marketing   | Dev        |
| Sales       | Vikram     |
+-------------+------------+

Only four departments appear. Research and Support are gone, because their rows have a NULL salary. Moving the condition into ON keeps all departments:

sql
SELECT d.name AS department, e.first_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id AND e.salary > 100000
ORDER BY d.name, e.first_name;
result
+-------------+------------+
| department  | first_name |
+-------------+------------+
| Engineering | Arjun      |
| Engineering | Kavya      |
| Engineering | Meera      |
| Finance     | Lata       |
| Marketing   | Dev        |
| Research    | NULL       |
| Sales       | Vikram     |
| Support     | NULL       |
+-------------+------------+

10. UPDATE or DELETE without a safe WHERE

The most expensive mistake does not show a result at all: UPDATE employees SET salary = 0; or DELETE FROM employees; change every row. It is not run here. The habits that prevent it:

  • Write the SELECT with the same WHERE first and read the rows.
  • Run the change inside a transaction, check the count, then COMMIT.
  • Filter by the primary key when you mean one row.

Here is the check-first habit on a harmless example:

sql
SELECT COUNT(*) AS rows_to_change FROM employees WHERE department_id = 5;
result
+----------------+
| rows_to_change |
+----------------+
|              3 |
+----------------+

Differences between databases

NULL handling, NOT IN behaviour and LEFT JOIN semantics are the same everywhere. The differences are in the details: whether a non-grouped column is an error (SQLite allows it, most others do not), what / does with integers (MySQL returns a decimal, the others truncate), and how rows are returned when no ORDER BY is given (undefined everywhere). MySQL-specific traps are covered in the MySQL NULL values lesson.

Common mistakes

This whole lesson is the list, and one habit prevents most of it: after you write a query, ask "how many rows should this return?" and compare that with what you got.

Exercise

This query is meant to list every department with its number of employees, including Research, which has none. It returns the wrong thing. Find the problem and fix it, then confirm Research shows 0.

sql
SELECT d.name, COUNT(*) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.name;
Show one possible answer

COUNT(*) counts the single row produced for Research, giving 1. Count a column from the employee side instead, so unmatched rows count as 0:

sql
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY d.name;
result
+-------------+-----------+
|    name     | headcount |
+-------------+-----------+
| Engineering |         6 |
| Finance     |         2 |
| Marketing   |         3 |
| Research    |         0 |
| Sales       |         5 |
| Support     |         3 |
+-------------+-----------+

Key points

  • Most SQL mistakes do not raise an error; they return a plausible but wrong answer, so always sanity-check row counts and totals.
  • NULL is not equal to anything, including NULL: use IS NULL, and beware of NOT IN when the list contains NULL.
  • Joins decide which rows survive: a missing condition multiplies rows, an INNER JOIN drops unmatched rows, and a WHERE on the right table of a LEFT JOIN quietly turns it into an inner join.
  • Aggregates are filtered with HAVING, not WHERE, and every plain column in a grouped query must be in GROUP BY.
  • Divide integers with care, and never run UPDATE or DELETE without a WHERE that you have tested with a SELECT.

Check yourself

1. Why does WHERE manager_id = NULL return no rows?
2. You write FROM employees, departments with no join condition. 20 employees and 6 departments. How many rows?
3. Which condition filters groups by their count?
4. A LEFT JOIN from departments to employees stops showing departments with no employees after you add WHERE e.salary > 50000. Why?