NULL Handling in SQL

In SQL, NULL means "unknown" or "missing". It isn't zero, an empty string, or false. That single idea has far-reaching consequences: comparisons with NULL are neither true nor false but unknown, NULL = NULL isn't true, aggregates skip NULLs, and one NULL in a NOT IN list can make a query return nothing at all. These rules are consistent and well defined, but they're the source of countless subtle bugs: rows silently missing from results, wrong averages, and uniqueness constraints that don't constrain.

Understanding three-valued logic and the handful of NULL-aware tools (IS NULL, COALESCE, NULLIF, IS DISTINCT FROM) makes queries correct by construction. Designing schemas with NOT NULL wherever values are genuinely required avoids most problems in the first place.

TL;DR

• Comparisons with NULL return UNKNOWN; WHERE keeps only rows where the condition is TRUE.
• Test with IS NULL / IS NOT NULL, never = NULL.
• Aggregates ignore NULLs (except COUNT(*)); SUM of nothing is NULL.
• NOT IN (subquery) returns no rows if the subquery contains any NULL. Use NOT EXISTS.
• COALESCE(a, b, …) returns the first non-NULL value; NULLIF(a, b) returns NULL when a = b (handy for divide-by-zero).
• IS [NOT] DISTINCT FROM compares values treating NULLs as equal. Declare columns NOT NULL unless absence is meaningful.

Quick Example

Core Concepts

Three-Valued Logic

SQL boolean expressions evaluate to TRUE, FALSE, or UNKNOWN. Any comparison involving NULL (=, <>, <, >, LIKE) yields UNKNOWN. Logical operators follow these rules:

NOT UNKNOWN is UNKNOWN. WHERE, HAVING, ON, and CHECK treat UNKNOWN differently: WHERE/HAVING/ON keep only TRUE rows, but CHECK constraints pass on UNKNOWN. So CHECK (price > 0) allows NULL prices unless the column is also NOT NULL.

Testing and Comparing

• x IS NULL, x IS NOT NULL: the only reliable NULL tests.
• x IS DISTINCT FROM y: TRUE when values differ, treating two NULLs as equal and NULL vs a value as different. It's ideal for change detection (WHERE old.email IS DISTINCT FROM new.email) and nullable join keys. MySQL's equivalent is <=> (NOT (a <=> b)).
• x = y in JOIN … ON: rows with NULL keys never match, even to other NULLs.

NULL-Handling Functions

NULLs in Aggregates

• COUNT(*) counts rows; COUNT(col) skips NULLs.
• SUM, AVG, MIN, and MAX ignore NULLs. AVG(rating) over (5, NULL, 3) is 4, not 2.67.
• Aggregating an empty set gives NULL (except COUNT, which gives 0). Use COALESCE(SUM(x), 0).

See GROUP BY & aggregation.

NULLs in Joins, Sorting, and Grouping

• Outer joins produce NULLs for unmatched rows, and filtering on those columns in WHERE can turn the join inner. See SQL joins.
• GROUP BY treats all NULLs as one group.
• ORDER BY: NULLs sort last in ascending order in PostgreSQL and Oracle, and first in MySQL and SQL Server. Use NULLS FIRST / NULLS LAST for portability.
• DISTINCT and UNION treat NULLs as equal (duplicates are removed).

NULLs and Uniqueness

A standard UNIQUE constraint allows multiple NULLs, because NULLs aren't equal to each other. That's often intended (optional unique email), sometimes not. PostgreSQL 15+ supports UNIQUE NULLS NOT DISTINCT to allow only one NULL. Partial unique indexes (WHERE deleted_at IS NULL) handle soft-delete patterns.

String and Arithmetic Propagation

Most operators propagate NULL: NULL + 1 is NULL, and 'abc' || NULL is NULL in PostgreSQL. Oracle treats empty strings as NULL, which is a notorious portability issue. Use CONCAT() (which skips NULLs in many databases) or COALESCE when building strings.

Best Practices

Declare NOT NULL by Default

Make every column NOT NULL unless "unknown" or "not applicable" is a real state. Required fields should be enforced by the database, not just the application. Fewer nullable columns means fewer three-valued logic surprises. See data quality management.

Give NULL a Single Meaning Per Column

If NULL could mean "not provided", "not applicable", or "not yet computed", queries can't tell them apart. Use explicit values, status columns, or separate tables when those states matter.

Use NOT EXISTS Instead of NOT IN

NOT EXISTS behaves intuitively with NULLs and optimizes well. Reserve NOT IN for literal lists you know are non-NULL.

Be Explicit in Comparisons on Nullable Columns

When filtering nullable columns with <>, NOT LIKE, or ranges, decide whether NULL rows should be included, and write OR col IS NULL or IS DISTINCT FROM accordingly.

Common Mistakes

Inequality Filters Dropping NULL Rows

COALESCE Hiding Data Problems

COALESCE(amount, 0) in a revenue report makes missing data look like zero revenue. Use it for display, but investigate and fix the source of unexpected NULLs.

Assuming Empty String Equals NULL

WHERE name IS NULL doesn't match '' (except in Oracle). Normalize empty strings at write time, or check both: WHERE NULLIF(TRIM(name), '') IS NULL.

FAQ

Why doesn't WHERE column = NULL work?

Because comparing anything with NULL, including another NULL, yields UNKNOWN, not TRUE, and WHERE only keeps TRUE rows. Use IS NULL. Some databases have legacy settings (SQL Server's ANSI_NULLS OFF) that change this, but relying on them isn't portable.

Why does NOT IN return no rows?

If the subquery or list contains a NULL, x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The last comparison is UNKNOWN, so the whole expression is never TRUE. Filter NULLs out of the subquery, or use NOT EXISTS.

What's the difference between COALESCE and ISNULL/IFNULL?

COALESCE is standard SQL and accepts any number of arguments. ISNULL (SQL Server), IFNULL (MySQL, SQLite), and NVL (Oracle) are vendor-specific two-argument versions, with slight differences in type handling. Prefer COALESCE for portability.

Should I use NULL or a default value?

Use NULL when a value is genuinely unknown or not applicable, and design queries for it. Use a default (0, '', now()) when a sensible value exists and "unknown" isn't meaningful. Avoid magic values like -1 or '1900-01-01' standing in for unknown; they corrupt aggregates and confuse readers.

Related Topics

• SQL — The language overview
• SQL Joins — NULLs from outer joins and anti joins
• SQL GROUP BY & Aggregation — How aggregates treat NULL
• SQL CTEs & Subqueries — EXISTS vs IN
• Data Quality Management — Constraints and validation
• PostgreSQL — NULLS NOT DISTINCT and IS DISTINCT FROM

References

• PostgreSQL Docs — Comparison Functions and Operators
• PostgreSQL Docs — Conditional Expressions (COALESCE, NULLIF)
• Markus Winand — The Three-Valued Logic of SQL