Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL NULL Handling: COALESCE, NULLIF, IS NULL, and Three-Valued Logic

Why every comparison with NULL is UNKNOWN, why NOT IN can return nothing, and how unique indexes, sorting and NOT NULL migrations behave with NULL.

~5 min readSQL
#SQL#Type System#Best Practices
3D illustration of missing NULL voids intercepted by COALESCE fallback bridges with a ternary truth table TRUE FALSE UNKNOWN

Quick Summary

NULL means "unknown", not "empty" and not "zero". SQL uses three-valued logic: every comparison with NULL yields UNKNOWN, and WHERE keeps only rows where the predicate is TRUE. That is why WHERE deleted_at = NULL returns nothing, why NOT IN against a nullable column can return nothing, and why NULL + 1 is NULL rather than an error.

COALESCE returns the first non-NULL argument and is the standard fallback. NULLIF(a, b) returns NULL when the two are equal, which makes it the clean way to guard a division or convert a sentinel to a real NULL. IS NULL / IS DISTINCT FROM are the only correct ways to test for NULL. Everything else in this area is a dialect difference waiting to surprise you: '' is NULL in Oracle, NULL sorts first in MySQL and last in PostgreSQL, and a UNIQUE index allows any number of NULLs.

Basic syntax and query signatures

sql
SELECT COALESCE(nickname, display_name, 'anonymous') AS shown_name, NULLIF(divisor, 0) AS safe_divisor, numerator / NULLIF(divisor, 0) AS ratio, CASE WHEN deleted_at IS NULL THEN 'active' ELSE 'deleted' END AS state FROM accounts;

COALESCE takes any number of arguments and returns the first non-NULL; it is standard and available everywhere. The two-argument shortcuts are dialect-specific and worth knowing only for reading other people's SQL: IFNULL (MySQL, SQLite), ISNULL (SQL Server), NVL (Oracle). NULLIF(a, b) is standard everywhere.

Predicates:

sql
WHERE col IS NULL -- correct null test WHERE col IS NOT NULL WHERE col IS DISTINCT FROM 'x' -- null-safe inequality (PostgreSQL, standard) WHERE col <> 'x' -- excludes rows where col IS NULL

Key comparison table

ExpressionResult when col is NULLNote
col = NULLUNKNOWNNever TRUE; use IS NULL
col <> 'x'UNKNOWNThe row is filtered out
NOT (col = 'x')UNKNOWNSame, not TRUE
col IS DISTINCT FROM 'x'TRUENull-safe inequality
COALESCE(col, 0)0Type must unify
NULLIF(col, '')NULLConverts a sentinel to NULL
`col'x'`
COUNT(col)Not countedCOUNT(*) counts the row
SUM(col) over all-NULLNULLNot 0
AVG(col)Excluded from the denominatorAverages rise
BehaviourPostgreSQLMySQLSQL ServerOracle
'' = NULLNoNoNoYes
ORDER BY col NULL positionLast for ASCFirst for ASCFirst for ASCLast for ASC
Multiple NULLs in UNIQUEAllowedAllowedOne onlyAllowed
Null-safe equalityIS NOT DISTINCT FROM<=>IS NOT DISTINCT FROM (2022)DECODE trick

Practical edge cases and deep dives

1. NOT IN with a nullable column returns nothing

sql
-- Returns zero rows if any customer_id in orders is NULL SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and the final term is UNKNOWN, so no row can be TRUE. The fix is NOT EXISTS, which is not affected by NULL; or filter the subquery (WHERE customer_id IS NOT NULL), which works but depends on nobody removing the guard. This is the single most common NULL bug in production SQL, and it fails silently with an empty result rather than an error.

The same logic explains why WHERE status <> 'cancelled' hides rows with status IS NULL: an unknown status is not "not cancelled".

2. COALESCE types must unify

sql
-- ERROR: COALESCE types text and integer cannot be matched SELECT COALESCE(name, 0) FROM users; -- Correct: pick a value of the same type SELECT COALESCE(name, 'unnamed') FROM users;

The result type is resolved across all arguments, and PostgreSQL refuses to guess. MySQL is looser and will coerce — which is worse, because COALESCE(int_col, 'n/a') silently returns a string column and breaks arithmetic downstream.

COALESCE also short-circuits: later arguments are not evaluated once one is non-NULL. That makes COALESCE(json_col->>'a', json_col->>'b') safe, but it does not make COALESCE(sum(x)/count(x), 0) cheaper — the division is evaluated before COALESCE sees the argument, so use NULLIF on the divisor instead.

3. Unique constraints and NULL

sql
CREATE TABLE users (email text UNIQUE); -- PostgreSQL: many NULLs allowed INSERT INTO users (email) VALUES (NULL), (NULL); -- succeeds twice

Standard SQL treats NULLs as distinct for uniqueness, so a unique index does not prevent duplicate "missing" values. That is usually fine for a genuinely optional field and wrong for a natural key. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to change the rule for an index:

sql
CREATE UNIQUE INDEX users_email_uq ON users (email) NULLS NOT DISTINCT;

SQL Server goes the other way and permits only one NULL per unique index, which surprises people migrating from PostgreSQL. And a UNIQUE (a, b) composite allows duplicate (1, NULL) rows for the same reason — the comparison with NULL is never TRUE, so no violation is detected.

4. Sorting, grouping, and reporting

sql
SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status NULLS LAST; -- PostgreSQL / standard

GROUP BY puts all NULLs into one group, which is correct and often surprising: a report by region shows a NULL row that no lookup table can explain. ORDER BY puts them first or last depending on the engine and the direction — PostgreSQL defaults to NULLS LAST for ASC and NULLS FIRST for DESC, while MySQL and SQL Server put them first for ASC. Explicit NULLS FIRST/LAST (unsupported in MySQL and older SQL Server) or a CASE sort key removes the ambiguity.

In reports, decide early whether a missing value is 0, '', or a separate "unknown" bucket. COALESCE at the presentation layer and IS NULL at the analysis layer is the combination that keeps both honest.

5. Adding NOT NULL to a live table

sql
-- Fails if any existing row is NULL ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL; -- ERROR: column "customer_id" of relation "orders" contains null values (23502)

On a large table, SET NOT NULL takes an ACCESS EXCLUSIVE lock and scans every row. PostgreSQL 12+ can skip the scan if a validated CHECK constraint proves the condition, which makes the change fast:

sql
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_nn CHECK (customer_id IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_nn; -- SHARE UPDATE EXCLUSIVE only ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL; -- now instant ALTER TABLE orders DROP CONSTRAINT orders_customer_id_nn;

Backfill first, then constrain. A DEFAULT does not help existing rows — it only applies to new inserts — and a DEFAULT 0 on an id column creates a row pointing at nothing, which is worse than the NULL it replaced.

6. NULL in indexes and statistics

sql
CREATE INDEX idx_orders_open ON orders (customer_id) WHERE deleted_at IS NULL; CREATE INDEX idx_users_name ON users (COALESCE(display_name, ''));

A B-tree index stores NULL entries, so WHERE col IS NULL is index-supported, but a query with WHERE col = 'x' will never match those entries — which is why a partial index on WHERE col IS NOT NULL is smaller and faster for the populated subset. The planner also relies on statistics that record the fraction of NULLs per column; after a bulk load of mostly-NULL rows, ANALYZE is what lets it choose the partial index instead of a sequential scan.

Common mistakes and runtime errors

Error or symptomCauseFix
Query returns no rows though data exists= NULL or <> with nullable columnIS NULL, IS DISTINCT FROM
NOT IN returns nothingNULL in the subquery resultNOT EXISTS
null value in column "x" violates not-null constraint (23502)Insert/update without a valueSupply a value or COALESCE a default
COALESCE types text and integer cannot be matchedMixed argument typesCast all arguments to one type
a + NULL or `aNULLreturnsNULL`
Avg looks inflatedAVG excludes NULL from the denominatorAVG(COALESCE(x, 0)) if zeros belong
Duplicate rows despite a UNIQUE indexNULLs are distinct by defaultNULLS NOT DISTINCT, or a partial unique index
ISNULL/NVL not recognisedDialect-specific functionCOALESCE
Empty string behaves as NULL on Oracle'' is NULL thereTest with IS NULL, normalise input
SET NOT NULL takes a table lock for minutesFull table scan under ACCESS EXCLUSIVEBackfill, then CHECK ... NOT VALID → VALIDATE

The mindset that prevents most of these: treat NULL as a third state that must be handled explicitly at every boundary — in WHERE, in CHECK constraints, in NOT NULL on the columns that matter, and at the edges where data enters the system. A database that allows NULL everywhere forces every query to answer "what does missing mean here?" separately, and the answer drifts.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.