SQL NULL Values Explained: IS NULL, COALESCE, NULLIF and the Traps to Avoid

A query runs without errors and returns a sensible-looking number, but rows are missing and the average is wrong. SQL NULL values are very often the cause. NULL does not behave like any normal value, and that catches out beginners and experienced developers alike.

This guide shows exactly how NULL behaves. We ran every query in SQLite 3.50, and the results shown come from that run. Most of this behaviour is standard SQL, and we note where databases differ, such as NULL sort order, and Oracle’s treatment of empty strings.

What is NULL?

NULL is a marker meaning that a value is missing or unknown. It is not a value in the usual sense, so these are all different things:

The difference matters for amounts especially. A bonus of 0 means the person received nothing. A NULL bonus means nobody knows yet.

The sample table

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Notice that Sara has an empty string for the phone, whereas Arjun and Ravi have NULL. The two are treated very differently.

Testing for NULL

Here is the mistake almost everyone makes at first:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

The first query is valid SQL and gives no error. It simply returns nothing, because comparing anything with NULL is never true. Sara shows up under IS NOT NULL, since an empty string is a real value. In Oracle, though, an empty string is treated as NULL, which is a notable exception.

Three-valued logic

In most programming languages, a condition is true or false. SQL adds a third result: unknown. If you do not know a value, you cannot say whether it equals 5. And two unknown values are not known to be equal, which is why NULL = NULL is not true. In our test, all of these returned NULL:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

Combining conditions follows careful rules. Here are the results from our run, where 1 is true and 0 is false:

Rows that vanish from WHERE

WHERE keeps a row only if its condition is true. Rows where the result is unknown get dropped, along with those that are false. This leads to surprising results:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

You might expect Arjun and Ravi to appear, since their phone certainly is not that number. But their phone is unknown, so the result is unknown, and they vanish. To include them, say so explicitly: WHERE phone <> '555-0101' OR phone IS NULL. Negation does not help either:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara and Ravi have no bonus, so they appear in neither bonus > 400 nor its opposite. This is a key thing to remember: a condition and its negation do not always cover every row.

COALESCE: provide a fallback

COALESCE returns the first non-NULL value in its list, and it works across all major databases:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara still appears blank, because an empty string is not NULL. Similar functions exist, such as IFNULL in MySQL and SQLite, ISNULL in SQL Server and NVL in Oracle. COALESCE is the portable choice. Our earlier point about calculations applies here too. bonus + 100 gives NULL for anyone without a bonus, so write COALESCE(bonus, 0) + 100 if treating a missing bonus as zero is correct for your business.

NULLIF: turn a value into NULL

NULLIF(a, b) returns NULL if the two values are equal, and otherwise returns a. Two uses are especially helpful. It can clean up placeholder values:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

That second query is a handy way to find values that are “missing or blank”. It can also guard against dividing by zero, which raises an error in many databases:

SELECT total / NULLIF(quantity, 0) FROM sales;

When quantity is 0, this gives NULL rather than failing. In our test, SELECT 100 / NULLIF(0, 0) returned NULL.

Aggregates skip NULL

This is where NULL does the most damage to reports:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

So what is the average bonus, 400 or 200? It depends on what NULL means in your data. If NULL means “no bonus was paid”, then 200 is right. If it means “not yet decided”, then 400 may be right. The database cannot make this decision for you, so the question must go to someone who knows the business.

A related trap: SUM of no values is NULL rather than 0. When we summed only rows with no bonus, we got NULL, while COUNT returned 0. Application code expecting a number can crash on this, so wrap it: COALESCE(SUM(bonus), 0).

GROUP BY, sorting and DISTINCT

Although NULLs are “not equal” in comparisons, these operations treat them as one group:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

See SQL GROUP BY and HAVING for more on grouping. Sort order is where databases really differ. SQLite and MySQL treat NULL as the smallest value, while PostgreSQL and Oracle treat it as the largest. So the same ORDER BY bonus DESC can put NULLs last in one and first in another. PostgreSQL, Oracle and modern SQLite let you state this directly, using NULLS FIRST or NULLS LAST.

Joins

A join condition using = never matches NULL, so rows with a NULL key drop out of an inner join:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera has no manager, so an inner join silently drops that row. A LEFT JOIN keeps it, and it also creates NULLs of its own for the missing side. Read more in SQL joins explained.

The NOT IN trap

A single NULL in a NOT IN list wipes out every result:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

The second form means manager_id <> 1 AND manager_id <> NULL, and that last part can never be true. This is particularly dangerous with subqueries, where a NULL can arrive unnoticed. Use NOT EXISTS instead, as explained in our SQL subqueries tutorial. Meera is also missing from the first result, since that row’s manager_id is NULL.

Designing with NULL

• Use NOT NULL by default. Allow NULL only when a value can truly be missing.
• Add sensible defaults. For example, quantity INT NOT NULL DEFAULT 0.
• Do not use magic values. A date of 1900-01-01 or an age of -1 standing in for “unknown” will corrupt your calculations.
• Know the UNIQUE behaviour. Most databases allow several NULLs in a UNIQUE column, and our test inserted two with no error. SQL Server is an exception, as it normally allows one.
• Remember that a primary key cannot be NULL. See primary key vs foreign key.
• Test with NULLs. Test data with no NULLs hides these bugs. Include them on purpose, as you would other edge cases.

Final thoughts

Treat NULL as “unknown”, and most of its behaviour starts to make sense: unknown plus one is unknown, and unknown equals unknown is not known to be true. Audit one of your own reports this week. Check nullable columns using COUNT(*) against COUNT(column), look for any NOT IN, and decide what NULL should mean in each average.

Frequently asked questions

Keep learning