SQL NULL Values: IS NULL, IS NOT NULL, COALESCE Explained

Sometimes a value is missing: an employee has not given an email, a customer has no phone number, an order has not shipped yet. SQL represents "no value" with a special marker called NULL. It behaves differently from every other value, and misunderstanding it causes some of the most common SQL bugs.

The example table#

What NULL means#

NULL means unknown or missing. It is not zero, not an empty string '', and not the word "NULL". A salary of 0 means "paid nothing"; a salary of NULL means "we don't know".

You cannot compare NULL with =#

Try finding employees with no manager the obvious way:

SELECT name
FROM employees
WHERE manager_id = NULL;

name
----
(0 rows)

No rows, even though two employees have no manager. The reason: comparing anything with an unknown value gives an unknown result, not true. "Is an unknown number equal to an unknown number?" Nobody knows, so SQL answers NULL, and WHERE only keeps rows where the condition is true.

IS NULL and IS NOT NULL#

SQL has special operators for missing values:

SELECT name, manager_id
FROM employees
WHERE manager_id IS NULL;

name  | manager_id
------+-----------
Meera | NULL
Priya | NULL

SELECT name, email
FROM employees
WHERE email IS NOT NULL;

name  | email
------+------------------
Asha  | asha@company.com
Ravi  | ravi@company.com
Meera | meera@company.com
Priya | priya@company.com

Three-valued logic: the NOT trap#

Because conditions can be true, false, or unknown, SQL uses three-valued logic. This creates a surprise. Who does not report to manager 5?

SELECT name
FROM employees
WHERE manager_id <> 5;

name
----
Ravi

Only Ravi! Meera and Priya have no manager, so they clearly do not report to manager 5, but NULL <> 5 is unknown, so they are dropped. To include them, say so explicitly:

SELECT name
FROM employees
WHERE manager_id <> 5 OR manager_id IS NULL;

name
-----
Ravi
Meera
Priya

NULL in calculations#

Any arithmetic or text operation with NULL produces NULL:

NULL in aggregate functions#

Aggregate functions skip NULLs, except COUNT(*), which counts rows:

SELECT COUNT(*) AS all_rows,
       COUNT(email) AS with_email
FROM employees;

all_rows | with_email
---------+-----------
5        | 4

SUM, AVG, MIN and MAX also ignore NULLs. That means AVG(bonus) is the average of the employees who have a bonus, not of everyone.

Replace NULL with COALESCE#

COALESCE(a, b, c, ...) returns the first value that is not NULL. It is the standard way to show a default:

SELECT name,
       COALESCE(email, 'not provided') AS email
FROM employees;

name  | email
------+------------------
Asha  | asha@company.com
Ravi  | ravi@company.com
Meera | meera@company.com
John  | not provided
Priya | priya@company.com

COALESCE works in every major database. You will also see vendor-specific versions: IFNULL (MySQL, SQLite), ISNULL (SQL Server), NVL (Oracle).

The opposite function, NULLIF(a, b), returns NULL when a equals b. A common use is avoiding division by zero: total / NULLIF(count, 0).

NULL and sorting, grouping, DISTINCT#

• GROUP BY and DISTINCT treat all NULLs as one group.
• In ORDER BY, NULLs go first or last depending on the database (see ORDER BY).
• A UNIQUE column usually allows several NULLs, because unknown values are not considered equal (SQL Server is an exception).

Common mistakes#

Practice#

SELECT name, city
FROM employees
WHERE email IS NULL OR manager_id IS NULL
ORDER BY name;

name  | city
------+-------
John  | Mumbai
Meera | Pune
Priya | Delhi

Key takeaway#