Working with NULL

NULL is the value SQL uses when a value is missing or unknown. It is not zero and not empty text. Interviewers ask about NULL because it causes more wrong answers than any other SQL topic, and the same mistakes cause quiet bugs in test queries: a check that should find rows finds none, and nobody notices. In the company data, four employees have no bonus recorded, one has no department, and the top manager has no manager.

Three-valued logic

A comparison with NULL is neither true nor false. It is unknown. WHERE keeps only rows where the condition is true, so unknown rows drop out. That is why this query finds nothing, even though four bonuses are NULL:

SELECT name FROM employees WHERE bonus = NULL;

name

To test for a missing value, use IS NULL or IS NOT NULL:

SELECT name FROM employees WHERE bonus IS NULL;

name
Vikram Shah
Rohan Mehta
Kavya Iyer
Sneha Patil

The "not equal" trap

Say you want every employee whose bonus is not 5,000, and people with no bonus should count too. bonus <> 5000 is unknown for a NULL bonus, so those people vanish. Add them on purpose:

SELECT name, bonus FROM employees WHERE bonus <> 5000 OR bonus IS NULL;

name | bonus
Asha Rao | 20000
Vikram Shah | NULL
Rohan Mehta | NULL
Priya Nair | 3000
Arjun Das | 15000
Kavya Iyer | NULL
Imran Khan | 2000
Sneha Patil | NULL
Dev Malhotra | 8000

NOT (bonus = 5000) has the same problem. Whenever a column can be NULL, decide what should happen to those rows.

COALESCE: a value for "missing"

Arithmetic with NULL gives NULL, so salary + bonus is NULL for anyone without a bonus. COALESCE(a, b, ...) returns its first argument that is not NULL, which lets you choose a default:

SELECT name, salary, bonus, salary + bonus AS plain_sum,
       salary + COALESCE(bonus, 0) AS total_pay
FROM employees WHERE dept_id = 2;

name | salary | bonus | plain_sum | total_pay
Neha Gupta | 82000 | 5000 | 87000 | 87000
Rohan Mehta | 64000 | NULL | NULL | 64000
Priya Nair | 64000 | 3000 | 67000 | 67000

COALESCE is standard SQL and works everywhere. You will also meet IFNULL (MySQL and SQLite) and ISNULL (SQL Server), which take two inputs (called arguments). SQL Server is another popular database.

NULL in counts and averages

Aggregate functions work on many rows and return one value; the next topic, Aggregate functions, covers them. For now: COUNT counts, AVG averages and ROUND rounds to a whole number. Aggregate functions on a column skip NULLs. COUNT(*) counts rows, but COUNT(bonus) counts only rows with a bonus. AVG(bonus) divides by the number of known bonuses, not by the number of employees. Both averages below are "the average bonus", but they answer different questions:

SELECT COUNT(*) AS employees, COUNT(bonus) AS with_bonus,
       ROUND(AVG(bonus)) AS avg_known, ROUND(AVG(COALESCE(bonus, 0))) AS avg_all
FROM employees;

employees | with_bonus | avg_known | avg_all
10 | 6 | 8833 | 5300

Two more rules: SUM over no rows returns NULL, not 0. And GROUP BY and DISTINCT treat all NULLs as one value, even though NULL = NULL is unknown.

The NOT IN trap

This is a common follow-up question. "List employees who manage nobody" sounds like NOT IN (SELECT manager_id ...). The part in brackets is a subquery, a query inside another query (see Subqueries). It returns the list of manager ids, and that list contains a NULL, because the top manager has no manager. id NOT IN (1, 3, NULL) means id <> 1 AND id <> 3 AND id <> NULL, and the last part is always unknown. So the query returns nothing:

SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

name

Remove the NULLs from the list, or use NOT EXISTS, which only asks whether a matching row exists:

SELECT name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees x WHERE x.manager_id = e.id);

name
Rohan Mehta
Priya Nair
Kavya Iyer
Imran Khan
Sneha Patil
Dev Malhotra

Read it like this: for each employee e, look for a row x whose manager_id equals e.id. If no such row exists, e manages nobody. SELECT 1 only means "I do not need any column, I just want to know if a row exists".

Sorting NULLs

Where NULLs land in a sorted result depends on the database. SQLite and MySQL treat NULL as smaller than any value, so NULLs come first in ascending order. PostgreSQL does the opposite. PostgreSQL and SQLite accept NULLS FIRST and NULLS LAST; MySQL does not. A form that works in all three sorts on the NULL test first. bonus IS NULL is 0 for a known bonus and 1 for a missing one, so known bonuses come first:

SELECT name, bonus FROM employees
ORDER BY bonus IS NULL, bonus DESC, name;

name | bonus
Asha Rao | 20000
Arjun Das | 15000
Dev Malhotra | 8000
Neha Gupta | 5000
Priya Nair | 3000
Imran Khan | 2000
Kavya Iyer | NULL
Rohan Mehta | NULL
Sneha Patil | NULL
Vikram Shah | NULL

Empty text is not NULL

An empty string '' is a real value in SQLite, MySQL and PostgreSQL. (Oracle is the exception: it stores empty text as NULL.) A check for a required field therefore often needs both tests: WHERE email IS NULL OR email = ''.

How to answer in an interview

Lead with the definition: NULL means "no known value", and any comparison with it is unknown, so WHERE drops the row. Then give the fixes: IS NULL to find it, COALESCE to replace it, NOT EXISTS instead of NOT IN. Raise the NOT IN trap yourself before the interviewer does.

Common mistakes

• Writing = NULL or <> NULL instead of IS NULL or IS NOT NULL.
• Forgetting that <> also drops NULL rows.
• Adding a column that may be NULL without COALESCE.
• Expecting SUM of no rows to be 0.

Recap

NULL is unknown, and comparisons with it are unknown. Use IS NULL, COALESCE and NOT EXISTS. Aggregates skip NULLs, but COUNT(*) does not. Say where NULLs should sort instead of trusting the default.