Query Belt Coming to Google Play

SQL NULL and GROUP BY interview questions

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.

departments(id, name, budget)
employees(id, full_name, department_id, manager_id, salary, hired_on)
sales(id, sold_on, region, amount)

departments

idnamebudget
1Sales30000
2Engineering80000
3Support20000
4Finance15000

employees

idfull_namedepartment_idmanager_idsalaryhired_on
1Mara Lind2NULL92002019-03-04
2Joel Park2176002020-06-15
3Ines Cole2176002021-01-11
4Tomas Reyes1569002018-09-01
5Nadia Voss1NULL64002017-05-22
6Owen Hart1543002022-02-14
7Pia Novak3839002023-04-03
8Sven Ito3NULL52002020-11-30
9Lea BrandtNULLNULL48002024-01-08
10Ravi Shah2161002022-08-19

sales

idsold_onregionamount
12026-01-01North120
22026-01-02South80
32026-01-03North150
42026-01-04SouthNULL
52026-01-05North90
62026-01-06South200
72026-01-07North110

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 IS NULL
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 NOT IN (SELECT department_id FROM employees)
name
Result: 0 rows · wrong: Lea's NULL department empties the result
SELECT name FROM departments
WHERE id NOT IN (SELECT department_id FROM employees WHERE department_id IS NOT NULL)
name
Finance
Result: 1 row · fixed by removing NULLs
SELECT name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM 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.

SELECT COUNT(*) AS all_rows, COUNT(amount) AS with_amount, COUNT(*) - COUNT(amount) AS missing FROM sales
all_rowswith_amountmissing
761
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.

SELECT ROUND(AVG(amount), 2) AS ignoring_null,
       ROUND(AVG(COALESCE(amount, 0)), 2) AS null_as_zero
FROM sales
ignoring_nullnull_as_zero
125107.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 ORDER BY manager_id, id
full_namemanager_id
Mara LindNULL
Nadia VossNULL
Sven ItoNULL
Lea BrandtNULL
Joel Park1
Ines Cole1
Ravi Shah1
Tomas Reyes5
Owen Hart5
Pia Novak8
Result: 10 rows · SQLite: the managers without a manager come first
SELECT full_name, manager_id FROM employees ORDER BY manager_id IS NULL, manager_id, id
full_namemanager_id
Joel Park1
Ines Cole1
Ravi Shah1
Tomas Reyes5
Owen Hart5
Pia Novak8
Mara LindNULL
Nadia VossNULL
Sven ItoNULL
Lea BrandtNULL
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 GROUP BY department_id ORDER BY department_id
department_idn
NULL1
13
24
32
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
GROUP BY department_id
ORDER BY department_id
department_idfull_nametop_salary
NULLLea Brandt4800
1Tomas Reyes6900
2Mara Lind9200
3Sven Ito5200
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(CASE WHEN salary >= 6000 THEN 1 ELSE 0 END) AS paid_6000_plus
FROM employees
GROUP BY department_id
ORDER BY department_id
department_idtotalpaid_6000_plus
NULL10
132
244
320
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.

SELECT 7 / 2 AS int_div, 7 / 2.0 AS real_div, 7 % 2 AS remainder, 10 / NULLIF(0, 0) AS safe_div
int_divreal_divremaindersafe_div
33.51NULL
Result: 1 row

Keep going

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.

Coming to Google Play