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.
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:
SELECT first_name FROM employees WHERE manager_id IS NULL ORDER BY id;+------------+
| 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:
SELECT COUNT(*) AS all_rows, COUNT(manager_id) AS with_manager, COUNT(department_id) AS with_department
FROM employees;+----------+--------------+-----------------+
| 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:
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:
SELECT department_id, COUNT(*) AS headcount, MIN(hire_date) AS first_hire
FROM employees
GROUP BY department_id
ORDER BY department_id;+---------------+-----------+------------+
| 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:
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE COUNT(*) > 3
GROUP BY department_id;Parse error near line 1: misuse of aggregate: COUNT()Fixed:
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3
ORDER BY department_id;+---------------+-----------+
| 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:
SELECT COUNT(*) AS rows_returned FROM employees, departments;+---------------+
| 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:
SELECT COUNT(*) AS rows_returned
FROM employees e
JOIN departments d ON d.id = e.department_id;+---------------+
| 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:
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;+------------+------------+
| 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:
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:
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;+------------+
| 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:
SELECT 7 / 2 AS integer_division, 7 / 2.0 AS decimal_division;+------------------+------------------+
| 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:
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;+-------------+------------+
| 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:
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;+-------------+------------+
| 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
SELECTwith the sameWHEREfirst 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:
SELECT COUNT(*) AS rows_to_change FROM employees WHERE department_id = 5;+----------------+
| 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.
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:
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;+-------------+-----------+
| 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.