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.

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
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:
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 NULLKey comparison table
| Expression | Result when col is NULL | Note |
|---|---|---|
col = NULL | UNKNOWN | Never TRUE; use IS NULL |
col <> 'x' | UNKNOWN | The row is filtered out |
NOT (col = 'x') | UNKNOWN | Same, not TRUE |
col IS DISTINCT FROM 'x' | TRUE | Null-safe inequality |
COALESCE(col, 0) | 0 | Type must unify |
NULLIF(col, '') | NULL | Converts a sentinel to NULL |
| `col | 'x'` | |
COUNT(col) | Not counted | COUNT(*) counts the row |
SUM(col) over all-NULL | NULL | Not 0 |
AVG(col) | Excluded from the denominator | Averages rise |
| Behaviour | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|
'' = NULL | No | No | No | Yes |
ORDER BY col NULL position | Last for ASC | First for ASC | First for ASC | Last for ASC |
Multiple NULLs in UNIQUE | Allowed | Allowed | One only | Allowed |
| Null-safe equality | IS 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
-- 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
-- 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
CREATE TABLE users (email text UNIQUE); -- PostgreSQL: many NULLs allowed
INSERT INTO users (email) VALUES (NULL), (NULL); -- succeeds twiceStandard 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:
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
SELECT status, COUNT(*)
FROM orders
GROUP BY status
ORDER BY status NULLS LAST; -- PostgreSQL / standardGROUP 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
-- 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:
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
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 symptom | Cause | Fix |
|---|---|---|
| Query returns no rows though data exists | = NULL or <> with nullable column | IS NULL, IS DISTINCT FROM |
NOT IN returns nothing | NULL in the subquery result | NOT EXISTS |
null value in column "x" violates not-null constraint (23502) | Insert/update without a value | Supply a value or COALESCE a default |
COALESCE types text and integer cannot be matched | Mixed argument types | Cast all arguments to one type |
a + NULL or `a | NULLreturnsNULL` | |
| Avg looks inflated | AVG excludes NULL from the denominator | AVG(COALESCE(x, 0)) if zeros belong |
Duplicate rows despite a UNIQUE index | NULLs are distinct by default | NULLS NOT DISTINCT, or a partial unique index |
ISNULL/NVL not recognised | Dialect-specific function | COALESCE |
Empty string behaves as NULL on Oracle | '' is NULL there | Test with IS NULL, normalise input |
SET NOT NULL takes a table lock for minutes | Full table scan under ACCESS EXCLUSIVE | Backfill, 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 toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.