NULLs and Three-Valued Logic

TL;DR

In SQL, NULL is not a value — it’s a marker meaning “unknown” or “not applicable”. Comparing anything to NULL yields a third truth value: UNKNOWN. SQL therefore has three-valued logic — TRUE, FALSE, UNKNOWN — and WHERE only keeps rows that evaluate to TRUE.

The consequences are everywhere:

• WHERE x = NULL returns no rows, even when x IS NULL.
• WHERE x <> 5 excludes rows where x IS NULL.
• NOT IN (… NULL …) returns no rows.
• NULL = NULL is unknown, so joins on nullable columns silently drop NULL pairs.
• COUNT(*) includes NULL rows; COUNT(col) skips them; SUM(col) skips them.
• NULL is generally sorted last in ASC (Postgres default), first in DESC.

Internalize “NULL is unknown, not zero, not empty string” and 80% of SQL’s sharp edges become predictable.

Truth tables for three-valued logic

AND:

OR:

NOT:

The pattern: AND is conservative — FALSE short-circuits to FALSE even with NULLs involved. OR is optimistic — TRUE short-circuits to TRUE. NULL propagates everywhere else.

A worked example

CREATE TABLE users (id int, email text, last_login timestamptz);
INSERT INTO users VALUES
  (1, 'alice@x.com', '2026-04-01 10:00+00'),
  (2, 'bob@x.com',   NULL),
  (3, NULL,          '2026-04-15 12:00+00'),
  (4, NULL,          NULL);

Try each query and predict the row count:

SELECT COUNT(*) FROM users;                            -- 4
SELECT COUNT(email) FROM users;                        -- 2 (NULLs skipped)
SELECT COUNT(*) FROM users WHERE email = NULL;         -- 0  ← surprise
SELECT COUNT(*) FROM users WHERE email IS NULL;        -- 2
SELECT COUNT(*) FROM users WHERE email <> 'alice@x.com'; -- 1 (only Bob)
SELECT COUNT(*) FROM users WHERE email IS DISTINCT FROM 'alice@x.com'; -- 3

The killers:

• email = NULL is UNKNOWN for every row — no rows pass WHERE.
• email <> 'alice@x.com' is UNKNOWN for rows 3 and 4 (NULL email), so they’re excluded from a “not equals” filter. Most beginners think “not Alice” should include NULLs.
• IS DISTINCT FROM treats NULLs like values — two NULLs are not distinct, NULL and ‘alice’ are distinct. This is the NULL-safe comparison.

NULL-safe equality and inequality

IS [NOT] DISTINCT FROM is in Postgres, BigQuery, DuckDB. Snowflake uses EQUAL_NULL(a, b). MySQL uses <=> for null-safe equality.

NULL in joins

CREATE TABLE a (k int, va text);
CREATE TABLE b (k int, vb text);
INSERT INTO a VALUES (1, 'x'), (NULL, 'y');
INSERT INTO b VALUES (1, 'X'), (NULL, 'Y');

SELECT * FROM a JOIN b ON a.k = b.k;
-- Returns 1 row: (1, 'x', 1, 'X'). The NULLs do NOT match.

If you genuinely need NULLs to match (rare but real — usually means your schema lets NULL stand for “unknown but the same unknown”):

SELECT * FROM a JOIN b ON a.k IS NOT DISTINCT FROM b.k;
-- Returns 2 rows.

This breaks index usage — most planners can’t use a B-tree index for IS NOT DISTINCT FROM. The right fix is usually a schema change: replace NULL with a sentinel value that can compare equal.

NULL in aggregates

Most aggregates skip NULL. The reason: “average of NULL and 10” should be “10”, not “NULL or 5”. This is usually what you want. When you don’t, do AVG(COALESCE(col, 0)) — you’re telling SQL “treat NULL as zero” rather than “skip NULL”.

NULL in arithmetic and string ops

SELECT 1 + NULL;            -- NULL
SELECT 'a' || NULL;         -- NULL  (Postgres)
SELECT CONCAT('a', NULL);   -- 'a'   (CONCAT in PG/MySQL skips NULL)
SELECT NULL = NULL;         -- NULL  (always)

Arithmetic with NULL → NULL, always. String concatenation with || → NULL. CONCAT (function form) is more forgiving. If you want explicit fallback, COALESCE.

NULL in ORDER BY

Default behavior is dialect-dependent:

Always specify NULLS FIRST or NULLS LAST if you care:

SELECT * FROM users ORDER BY last_login DESC NULLS LAST;

NULL in DISTINCT, UNION, INTERSECT, EXCEPT

These all treat two NULLs as equal for the purpose of dedup. This is a deliberate special case in the SQL standard (because the alternative — NULL rows always survive dedup — would be useless).

SELECT DISTINCT x FROM (VALUES (1), (NULL), (NULL), (2)) AS t(x);
-- 3 rows: 1, 2, NULL  (the two NULLs are merged)

This is one of the few places NULLs behave like values. It bites when you later filter WHERE x <> 5 and your NULL row vanishes.

NULL in WHERE — the safe filter pattern

-- WRONG: excludes NULL rows from "not US" filter
SELECT * FROM users WHERE country <> 'US';

-- RIGHT: explicitly handle NULLs
SELECT * FROM users WHERE country <> 'US' OR country IS NULL;
-- or
SELECT * FROM users WHERE country IS DISTINCT FROM 'US';

The first query excludes anyone with no recorded country, which is almost never what “not US” should mean.

COALESCE, NULLIF, NVL — the toolbox

COALESCE(a, b, c)   -- first non-NULL of a, b, c (variadic)
NULLIF(a, b)        -- NULL if a = b, else a
NVL(a, b)           -- (Oracle/Snowflake) two-arg COALESCE
ISNULL(a, b)        -- (SQL Server) two-arg COALESCE; weird type rules

COALESCE is portable; use it.

Common pitfalls

• WHERE x = NULL returns no rows. Use IS NULL.
• WHERE x NOT IN (… NULL …) returns no rows. Use NOT EXISTS or filter NULLs out of the subquery.
• Joining on a nullable column silently drops NULL-NULL pairs. Use IS NOT DISTINCT FROM (slow) or fix the schema.
• COUNT(col) vs COUNT(*) — different things if col has NULLs.
• AVG(col) over a NULL-heavy column is the average of the non-NULL values, not NULL-as-zero. If that’s wrong, AVG(COALESCE(col, 0)).
• a + b returns NULL if either is NULL. Wrap with COALESCE for fallback semantics.
• String concatenation with || and a NULL returns NULL. Use CONCAT or COALESCE.

Production patterns for ML

1. Imputation in feature pipelines. Choose your imputation strategy explicitly per column:

SELECT user_id,
       COALESCE(spend_30d, 0)               AS spend_30d,    -- "missing = no spend"
       COALESCE(country, 'unknown')         AS country,      -- "missing = unknown"
       COALESCE(last_login,
                signed_up_at)               AS last_active   -- "missing = signup"
FROM   features;

The training data should never see raw NULLs unless your model handles them (gradient-boosted trees can; logistic regression can’t).

2. NULL-safe deduplication. When deduping events keyed by (user_id, session_id) where session_id is sometimes NULL:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY user_id,
                            COALESCE(session_id, '__null__')   -- treat NULLs as a value
               ORDER BY ts DESC
           ) AS rn
    FROM   events
)
SELECT * FROM ranked WHERE rn = 1;

Without the COALESCE, every NULL session_id row would be its own partition (NULL <> NULL) and dedup wouldn’t fire.

Resources

• PostgreSQL — comparison operators and NULL — postgresql.org/docs
• Use The Index, Luke — NULL in the database — use-the-index-luke.com/sql/where-clause/null
• PostgreSQL documentation — postgresql.org/docs
• Mode Analytics SQL tutorial — mode.com/sql-tutorial