NULL and grouping cause more wrong answers than any other part of SQL. Nine questions, each with the query, the real result and the reason.
By Andrii Boiko · Updated 2026-11-18 · Every result on this page was produced by running the query on the sample database below in SQLite 3.37.2.
The sample database
All examples use three small tables with made-up data. Note the traps built into them: one employee has no department, two employees have the same salary, one department has no employees, and one sale has no amount.
Why does WHERE department_id = NULL return no rows?
Comparing anything with NULL, even NULL itself, gives NULL (unknown), and WHERE keeps only true. The database does not complain, so the bug is silent. Use IS NULL or IS NOT NULL.
SELECT full_name FROM employees WHERE department_id = NULL
full_name
Result: 0 rows · no error, no rows
SELECT full_name FROM employees WHERE department_id ISNULL
full_name
Lea Brandt
Result: 1 row
Why does NOT IN return nothing when I expected Finance?
x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL. The last part is unknown, so the whole condition is never true. One NULL in the subquery empties the result. Fix it by filtering the NULLs out of the subquery or, better, using NOT EXISTS.
SELECT name FROM departments
WHERE id NOTIN (SELECT department_id FROM employees)
name
Result: 0 rows · wrong: Lea's NULL department empties the result
SELECT name FROM departments
WHERE id NOTIN (SELECT department_id FROM employees WHERE department_id ISNOTNULL)
name
Finance
Result: 1 row · fixed by removing NULLs
SELECT name FROM departments d
WHERENOTEXISTS (SELECT1FROM employees e WHERE e.department_id = d.id)
name
Finance
Result: 1 row · NOT EXISTS is safe
What is the difference between COUNT(*) and COUNT(column) when there are NULLs?
COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL. The difference is the number of NULLs.
SELECTCOUNT(*) AS all_rows, COUNT(amount) AS with_amount, COUNT(*) - COUNT(amount) AS missing FROM sales
all_rows
with_amount
missing
7
6
1
Result: 1 row
Does AVG treat NULL as zero?
No. AVG, SUM, MIN and MAX skip NULLs, so the average is over the rows that have a value. If a missing amount should count as zero, say so with COALESCE. The two answers differ, and which one is right depends on what the NULL means.
SELECTROUND(AVG(amount), 2) AS ignoring_null,
ROUND(AVG(COALESCE(amount, 0)), 2) AS null_as_zero
FROM sales
ignoring_null
null_as_zero
125
107.14
Result: 1 row
Where do NULLs appear when I ORDER BY?
It depends on the database. SQLite, MySQL and SQL Server put NULLs first in ascending order; PostgreSQL puts them last. PostgreSQL and recent SQLite versions accept NULLS FIRST / NULLS LAST. A portable trick is to sort on column IS NULL first.
SELECT full_name, manager_id FROM employees ORDERBY manager_id, id
full_name
manager_id
Mara Lind
NULL
Nadia Voss
NULL
Sven Ito
NULL
Lea Brandt
NULL
Joel Park
1
Ines Cole
1
Ravi Shah
1
Tomas Reyes
5
Owen Hart
5
Pia Novak
8
Result: 10 rows · SQLite: the managers without a manager come first
SELECT full_name, manager_id FROM employees ORDERBY manager_id ISNULL, manager_id, id
full_name
manager_id
Joel Park
1
Ines Cole
1
Ravi Shah
1
Tomas Reyes
5
Owen Hart
5
Pia Novak
8
Mara Lind
NULL
Nadia Voss
NULL
Sven Ito
NULL
Lea Brandt
NULL
Result: 10 rows · NULLs last, in every database
How does GROUP BY treat NULL values?
All NULLs fall into one group, even though NULL does not equal NULL in a comparison. DISTINCT behaves the same way. So rows with an unknown department are counted together as a group of their own.
SELECT department_id, COUNT(*) AS n FROM employees GROUPBY department_id ORDERBY department_id
department_id
n
NULL
1
1
3
2
4
3
2
Result: 4 rows
What happens if I select a column that is not in GROUP BY?
PostgreSQL and SQL Server reject the query. SQLite and (depending on settings) MySQL accept it. In SQLite, when the query has a single MAX() or MIN(), the extra column comes from the row that has the maximum or minimum, which looks like magic and works only in SQLite. In an interview, group by every non-aggregated column or use a window function.
SELECT department_id, full_name, MAX(salary) AS top_salary
FROM employees
GROUPBY department_id
ORDERBY department_id
department_id
full_name
top_salary
NULL
Lea Brandt
4800
1
Tomas Reyes
6900
2
Mara Lind
9200
3
Sven Ito
5200
Result: 4 rows · SQLite only: the name comes from the highest-paid row
Count how many people per department earn at least 6000.
Conditional aggregation: put a CASE inside the aggregate. SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts the matching rows while the same query still returns the group total. It replaces several queries with one and is the base of pivots.
SELECT department_id,
COUNT(*) AS total,
SUM(CASEWHEN salary >= 6000THEN1ELSE0END) AS paid_6000_plus
FROM employees
GROUPBY department_id
ORDERBY department_id
department_id
total
paid_6000_plus
NULL
1
0
1
3
2
2
4
4
3
2
0
Result: 4 rows
What does 7 / 2 return, and how do you avoid dividing by zero?
In SQLite, PostgreSQL and SQL Server, two integers divide as integers, so 7 / 2 is 3; MySQL returns 3.5. Make one side a decimal (7 / 2.0) when you want a fraction. Dividing by zero raises an error in PostgreSQL and SQL Server, and gives NULL in SQLite. NULLIF(divisor, 0) turns a zero divisor into NULL, so the same query behaves the same everywhere.
Practice it, don't just read it. Query Belt has a check that tells you when a NULL, a boundary value or a duplicate caused the difference, and the "Why is it empty?" helper for queries that return nothing.