SQL NULL Handling — Common Pitfalls and Fixes

1. What NULL Means (and What It Doesn't)

NULL marks the absence of a value. It is not zero, not an empty string, and not false. Those are all real values that happen to be small; NULL is the absence of information — the database has nothing to report for that cell.

The distinction matters because the first two are answerable questions and the third is not. "What is the balance?" has answer 0. "When did this ship?" has no answer at all.

NULL propagates through expressions. Any arithmetic or function call that touches NULL returns NULL unless the function is written to handle it explicitly:

SELECT
    NULL + 1                 AS a,   -- NULL  (arithmetic propagates)
    LENGTH(NULL)             AS b,   -- NULL  (string functions too)
    UPPER(NULL)              AS c,   -- NULL
    NULL < 5                AS d,   -- NULL  (comparison yields UNKNOWN)
    COALESCE(NULL + 1, 0)    AS e;   -- 0     (handled explicitly)

Two practical notes before the rest of the article:

• Oracle treats '' as NULL. INSERT INTO t (name) VALUES ('') stores a NULL in Oracle, so WHERE name IS NULL matches it. MySQL, PostgreSQL, and SQL Server keep the empty string as a real, distinct value. Any code relying on '' meaning "blank but present" behaves differently after an Oracle migration.
• Stop overloading sentinels. Columns that use -1, 0, or 'N/A' to mean "unknown" force every downstream query to know that convention and to remember it in every aggregate. Let NULL do its job for unknown data, and keep real values for real values.

2. Three-Valued Logic: TRUE, FALSE, UNKNOWN

SQL predicates are not two-valued. Any comparison involving NULL produces a third result, UNKNOWN, and WHERE filters it out because it keeps only rows where the condition is TRUE. UNKNOWN and FALSE are both discarded — which is why a NULL row disappears from col = 5 and from col <> 5.

AND truth table

FALSE wins in an AND: if one side is definitely false, the whole thing is false even when the other side is unknown.

OR truth table

TRUE wins in an OR, for the same reason: one known-true side settles it.

NOT

That last row is the one that bites. Negating an unknown predicate does not make it true — it stays unknown, and the row stays out of the result. A column status with values 'active', 'paused', and NULL behaves like this:

-- Rows with status = NULL are excluded from BOTH queries:
SELECT * FROM accounts WHERE status = 'active';      -- returns active only
SELECT * FROM accounts WHERE NOT (status = 'active'); -- returns paused only

If you want the NULL rows in that second result, say so explicitly: WHERE status IS NULL OR status <> 'active'. There is no shortcut — the engine cannot guess that you meant "everything except active" to include the rows it knows nothing about.

3. Why = NULL Never Matches

col = NULL produces UNKNOWN for every row — including rows where col is NULL, because NULL is not a value and "unknown = unknown" is still unknown. WHERE col = NULL therefore returns an empty set on a table full of NULLs. The operator is the problem, so SQL gives you a dedicated test instead of an equality operator: IS NULL and IS NOT NULL are the only correct way to ask about NULL.

-- All of these return zero rows, no matter how much data is NULL:
SELECT * FROM users WHERE last_login =  NULL;
SELECT * FROM users WHERE last_login != NULL;
SELECT * FROM users WHERE last_login <> '2026-01-01';  -- also drops the NULL rows

-- Correct forms:
SELECT * FROM users WHERE last_login IS NULL;
SELECT * FROM users WHERE last_login IS NOT NULL;

-- Null-safe equality: 'IS DISTINCT FROM' is true when values differ OR exactly one is NULL
-- (PostgreSQL, and SQL standard engines such as DuckDB and Oracle 23c)
SELECT * FROM users WHERE last_login IS DISTINCT FROM '2026-01-01';

The null-safe operators are worth knowing because they let you compare "including NULLs" without a pile of OR clauses:

A related trap that costs people an afternoon: <> and NOT IN look like they should return "everything else", but they silently exclude NULL rows. Whenever you write a negative predicate against a nullable column, decide explicitly whether NULL belongs in the answer.

4. The NOT IN Trap

This is the most damaging NULL bug in everyday SQL, because the query runs without error and returns an empty result that looks like a valid answer. Set up two small tables:

customers: id 1, 2, 3
orders:    (id 1, customer_id 1), (id 2, customer_id 1), (id 3, customer_id NULL)

-- "Which customers have never placed an order?"
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- Returns 0 rows. Expectation: customer 2 and 3.

One NULL customer_id in the subquery poisons the entire predicate. id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL. That second term is UNKNOWN for every row, and TRUE AND UNKNOWN is UNKNOWN — so no row ever passes. The subquery does not even need to contain a NULL that belongs to a real customer; a single nullable column with one missing value is enough.

Two fixes, both reliable:

-- Fix 1: NOT EXISTS with a correlated subquery. NULL-safe, and usually the best plan.
SELECT c.id
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Fix 2: keep NOT IN but strip NULLs inside the subquery.
SELECT id
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Both return customers 2 and 3. Prefer NOT EXISTS: it does not require the subquery to be NULL-free, it handles multi-column anti-joins naturally (NOT EXISTS (SELECT 1 FROM o WHERE o.a = c.a AND o.b = c.b)), and most optimizers turn it into an anti-join hash or merge instead of evaluating the list per row.

5. Aggregates Ignore NULL: COUNT(col) vs COUNT(*)

Every aggregate except COUNT(*) skips NULL inputs. SUM, AVG, MIN, and MAX evaluate only the rows where the argument is not NULL — and that changes the answers in ways that are easy to miss.

-- accounts.balance: 100, NULL, 200

SELECT
    COUNT(*)                   AS all_rows,        -- 3   every row
    COUNT(balance)             AS rows_with_data,  -- 2   NULL excluded
    SUM(balance)               AS total,           -- 300
    AVG(balance)               AS avg_balance,     -- 150 NOT 100
    COALESCE(SUM(balance), 0)  AS total_or_zero    -- 300 (0 when the group is all NULL)
FROM accounts;

The AVG result is the one that surprises people. It divides by the non-NULL count, not by the row count, so 300 / 2 = 150 rather than 300 / 3 = 100. If your business rule says a missing balance counts as zero, you must say so — AVG(COALESCE(balance, 0)) gives 100, and that is a deliberate analytical choice, not the database's job to infer.

Two more behaviors to internalize:

• An aggregate over zero non-NULL rows returns NULL, not 0. SUM(balance) on a group where every balance is NULL returns NULL; COUNT is the only aggregate that returns 0. When a dashboard shows a blank instead of "0", this is why — wrap it: COALESCE(SUM(balance), 0).
• GROUP BY treats NULLs as equal to each other. This is the opposite of WHERE. All NULL rows land in a single group, so you get one row with a NULL group key. DISTINCT and UNION behave the same way: they treat NULLs as not distinct and collapse them together. Equality in a predicate says "unknown"; grouping says "same bucket". Both are correct — they answer different questions.

6. COALESCE and the Dialect Zoo

COALESCE returns the first argument that is not NULL. It is SQL standard, exists in every major engine, and takes an arbitrary number of arguments evaluated left to right, stopping at the first non-NULL. Reach for it instead of vendor functions whenever the query might travel.

SELECT
    username,
    COALESCE(nickname, display_name, username, 'anonymous') AS shown_name,
    COALESCE(phone, 'not provided')                         AS contact_phone
FROM users;

-- Backfill a NULLable column with a real default value
UPDATE orders SET discount = 0 WHERE discount IS NULL;

The same idea goes by four different names across engines. They are not interchangeable in code you intend to port:

Two subtleties worth knowing before you pick a favorite:

• Return type resolution. In SQL Server, ISNULL(a, b) takes the data type of the first argument, so ISNULL(varchar_col, 'none') can truncate the replacement to the column's declared length. COALESCE follows the standard's type-resolution rules and returns the higher-precedence type. For strings, COALESCE is the safer choice.
• Short-circuit evaluation. COALESCE stops evaluating as soon as one argument is non-NULL, which is what makes it safe as a guard for a value that would otherwise error (see NULLIF below). Older vendor functions are not always documented to short-circuit, another reason the standard form is the default.

NULL and string concatenation

Concatenation propagates NULL. In PostgreSQL, Oracle, SQL Server, and MySQL's CONCAT, stitching a NULL into a string yields NULL for the whole expression — so a single missing middle name blanks out a full name built from three columns:

-- NULL if either column is NULL
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- Safe: neutralize each part first
SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name FROM users;

-- MySQL convention, which also collapses NULLs to empty strings
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM users;

CONCAT_WS ("with separator") skips NULL arguments entirely — a MySQL and SQL Server convenience whose Oracle and PostgreSQL equivalents you would have to build with COALESCE.

7. NULLIF: The Underrated One

NULLIF(a, b) returns NULL when a = b, and returns a otherwise. It sounds backward until you see the two jobs it does well.

Guard against division by zero

Dividing by zero is an error in PostgreSQL, MySQL, and SQL Server (Oracle raises it too), so a naive average-per-group query can fail on exactly the rows that have no data yet. Wrapping the denominator in NULLIF(expr, 0) converts the zero into NULL, and any arithmetic with NULL yields NULL — a missing answer instead of a crashed query:

-- Crashes when orders_count = 0
SELECT revenue / orders_count AS avg_order_value FROM daily_stats;

-- Returns NULL for those rows instead
SELECT revenue / NULLIF(orders_count, 0) AS avg_order_value FROM daily_stats;

-- With a readable fallback
SELECT COALESCE(revenue / NULLIF(orders_count, 0), 0) AS avg_order_value FROM daily_stats;

Turn a sentinel into a real NULL

Legacy tables love magic values — '' for "no email", 'N/A' for "unknown region". NULLIF normalizes them to NULL at read time so that IS NULL, aggregates, and COALESCE all behave consistently, without touching the stored data:

SELECT
    NULLIF(region_code, '')     AS region_code,   -- '' becomes NULL
    NULLIF(status, 'UNKNOWN')   AS status,        -- sentinel becomes NULL
    NULLIF(email, 'n/a')        AS email
FROM staging_imports;

Note that because NULLIF is built on equality, NULLIF(x, NULL) can never fire — the comparison is UNKNOWN. It only matches real values, which is fine for sentinel cleanup and useless for NULL detection, where IS NULL is what you want.

8. Sorting NULLs: ORDER BY and NULLS FIRST/LAST

Sort position for NULL differs by engine, but the SQL standard does not require a particular order — so a query that looks portable can produce two different reports. PostgreSQL and Oracle treat NULL as larger than any value (ascending puts NULLs last); MySQL, SQLite, and SQL Server treat it as smaller (ascending puts NULLs first).

Where the clause exists, use it explicitly rather than relying on engine defaults — it documents the intent and makes the query portable:

-- Most recent logins first, but actually seen users before never-seen ones
SELECT name, last_login
FROM users
ORDER BY last_login DESC NULLS LAST;

MySQL and SQL Server have no NULLS FIRST / NULLS LAST syntax, so you sort on a boolean sort key first. (col IS NULL) evaluates to 0 for real values and 1 for NULLs, so an ascending sort on that key pushes NULLs last:

-- MySQL / MariaDB: real values first, NULLs last
SELECT name, last_login FROM users ORDER BY last_login IS NULL, last_login DESC;

-- SQL Server: same idea with CASE
SELECT name, last_login FROM users
ORDER BY CASE WHEN last_login IS NULL THEN 1 ELSE 0 END, last_login DESC;

For NULLS FIRST, invert the key: ORDER BY last_login IS NOT NULL, last_login. And if NULLs must never appear in the output at all, filter instead of sorting around them — WHERE last_login IS NOT NULL is cheaper than any sort-key trick.

9. Schema Design: NOT NULL and DEFAULT

Half of production NULL bugs are decided at CREATE TABLE time. Two constraints do the work, and they do different jobs:

• NOT NULL rejects any attempt to store NULL, whether explicit or implicit. It is a real guarantee: readers of that column never need a NULL branch.
• DEFAULT supplies a value only when the column is omitted from an INSERT. It is not a guard — an explicit NULL still lands in the column unless NOT NULL is also present.

CREATE TABLE users (
    id          BIGINT PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    credits     INTEGER      NOT NULL DEFAULT 0,
    deleted_at  TIMESTAMP    NULL                      -- NULL = not deleted
);

-- DEFAULT applies: credits becomes 0
INSERT INTO users (id, email) VALUES (1, 'a@example.com');

-- DEFAULT is bypassed: this inserts NULL, which NOT NULL would reject
INSERT INTO users (id, email, credits) VALUES (2, 'b@example.com', NULL);
-- ERROR: null value in column "credits" violates not-null constraint

That is the single most misunderstood interaction in schema design. A column declared credits INTEGER DEFAULT 0 without NOT NULL will happily store NULL the moment a client sends an explicit NULL, and then every SUM(credits) silently drops those rows. If the column should always have a value, declare both: NOT NULL DEFAULT 0. The default handles the polite inserts; the constraint handles the rest.

Three more decisions that are cleaner when made up front:

• Use NULL for "unknown", a default for "known typical". signup_source that the application always knows should be NOT NULL with a sensible default. deleted_at is genuinely unknown until deletion, so it is correctly NULLable — and deleted_at IS NULL is a meaningful, indexable predicate.
• Unique indexes and NULL. PostgreSQL, MySQL, and Oracle allow many NULLs in a unique column, since two unknowns are not considered duplicates. SQL Server's unique index allows only one NULL in a single-column index. If "at most one row per NULL" matters, that is a constraint enforced in application code or with a partial/filtered unique index.
• CHECK constraints pass on UNKNOWN. CHECK (age > 0) rejects ages of 0 and -5 but accepts NULL, because the constraint fails only when the expression is FALSE, not when it is UNKNOWN. Add NOT NULL to the column if NULL should be disallowed — a CHECK will not do it for you.

FAQ — SQL NULL Handling