Databases & SQL Practice Question 3: Count and Null
Databases & SQL · Test 1 · question 3 of 10
The table employees(id, manager_id) contains five rows: (1, NULL), (2, 1), (3, 1), (4, NULL), (5, 2).
A developer runs these two queries. What numbers do they return, in order?
Query 1: SELECT COUNT(*), COUNT(manager_id) FROM employees;
Query 2: SELECT COUNT(*) FROM employees WHERE manager_id = NULL;
Show answer and explanation
Answer:
COUNT(*) counts input rows regardless of their contents, so it returns 5. COUNT(manager_id) counts only the rows where manager_id is not null. Rows 2, 3 and 5 qualify, so it returns 3.
In query 2, manager_id = NULL never evaluates to true, even for rows 1 and 4. Comparing anything to NULL with = gives NULL ("unknown"), and WHERE keeps only rows where the condition is true. No rows pass, and COUNT(*) over zero rows is 0. The correct test is WHERE manager_id IS NULL, which would return 2.
Option A assumes = NULL behaves like IS NULL. It does not, and that is the most common NULL bug in SQL.
Option C assumes COUNT(column) counts every row. That is what COUNT(*) does. Counting a column skips NULLs.
Option D assumes COUNT(*) also skips NULL rows. A row is counted by COUNT(*) even if some of its columns are NULL.
References: PostgreSQL: Comparison Functions and Operators (IS NULL vs = NULL); PostgreSQL: Aggregate Functions (count(*) vs count(expr))