NULL represents a missing, unknown, or undefined value.

Many people first notice it when they learn that: WHERE column = NULL doest not work as expected.

But NULL affects much more than comparisons. It changes how AND and OR are evaluated, how aggregates behave, how joins match rows, and how results are sorted.

Because of this, understanding NULL is essential in SQL. This chapter explains how NULL works and how to handle it correctly.

Insert the following rows to follow along:

The subscriptions table has end_date set to NULL for active subscriptions that haven't been cancelled. The payments table has refund_amount as NULL when no refund was issued (as opposed to 0.00, which would mean "refund considered but zero amount"). One of the stream rows have a NULL country. These NULL patterns give us realistic scenarios to work with.

Most programming languages use two-valued logic: conditions are either true or false. SQL uses three-valued logic. Every condition evaluates to one of TRUE, FALSE, or UNKNOWN.

UNKNOWN appears whenever NULL is involved in a comparison. Since NULL represents an unknown value, the database can't determine whether a comparison is true or false, so it returns UNKNOWN instead.

This matters because UNKNOWN is not the same as FALSE. WHERE clauses include only rows where the condition is TRUE. Rows that evaluate to UNKNOWN are excluded, just like rows that evaluate to FALSE. But the distinction between FALSE and UNKNOWN becomes critical when you combine conditions with AND, OR, and NOT.

AND with UNKNOWN

AND returns TRUE only when both sides are TRUE. If either side is FALSE, the result is FALSE regardless of the other side. But when UNKNOWN enters the picture:

The key insight: FALSE AND UNKNOWN is FALSE (not UNKNOWN), because no matter what the unknown value turns out to be, FALSE AND anything is FALSE. But TRUE AND UNKNOWN stays UNKNOWN, because the result depends on what the unknown value actually is.

The country = 'US' condition is TRUE for alice_m, bob_jones, and emma_dev. But timezone = NULL produces UNKNOWN for every row (even for emma_dev whose timezone actually is NULL). TRUE AND UNKNOWN gives UNKNOWN, so this query returns zero rows.

OR with UNKNOWN

OR returns TRUE when at least one side is TRUE. The full truth table with UNKNOWN:

TRUE OR UNKNOWN is TRUE, because it doesn't matter what the unknown value is. At least one side is already TRUE. But FALSE OR UNKNOWN stays UNKNOWN, because the result depends entirely on the unknown value.

For alice_m: 'US' = 'US' is TRUE, 'US' = NULL is UNKNOWN. TRUE OR UNKNOWN is TRUE, so alice_m appears. For gina_null: NULL = 'US' is UNKNOWN, NULL = NULL is UNKNOWN. UNKNOWN OR UNKNOWN is UNKNOWN, so gina_null is excluded. The OR country = NULL part does nothing useful. You need OR country IS NULL instead.

NOT with UNKNOWN

NOT flips TRUE to FALSE and FALSE to TRUE. But NOT UNKNOWN remains UNKNOWN:

This is why WHERE NOT (country = 'US') still excludes rows where country is NULL. The comparison NULL = 'US' produces UNKNOWN, and NOT UNKNOWN is still UNKNOWN.

The following diagram summarizes how SQL evaluates a WHERE condition when NULL is involved:

Only TRUE passes through WHERE. Both FALSE and UNKNOWN lead to exclusion.

IS NULL and IS NOT NULL are the only correct way to check for NULL values.

Checking Multiple Columns

When a row has several nullable columns, you sometimes need to check whether any of them are NULL, or whether all of them are:

This returns emma_dev (NULL timezone), frank_j (NULL timezone), and gina_null (both NULL). If you need users where both are missing:

This returns only gina_null.

IS NOT DISTINCT FROM

Standard SQL provides an operator that treats NULLs as equal to each other: IS NOT DISTINCT FROM. Where NULL = NULL produces UNKNOWN, NULL IS NOT DISTINCT FROM NULL produces TRUE.

This is equivalent to WHERE country IS NULL, but IS NOT DISTINCT FROM becomes more useful when comparing two columns or a column to a variable that might be NULL:

With regular =, if both u.country and s.country are NULL, the comparison produces UNKNOWN and the row is excluded. IS NOT DISTINCT FROM treats two NULLs as matching.

The inverse is IS DISTINCT FROM, which returns TRUE when values differ or when one is NULL and the other isn't:

Dialect Differences

MySQL's <=> operator (the "null-safe equal" operator) does the same thing as IS NOT DISTINCT FROM with less typing.

COALESCE takes a list of arguments and returns the first one that is not NULL. If all arguments are NULL, it returns NULL.

Providing Default Values

The most common use of COALESCE is replacing NULL with a meaningful default:

For gina_null, country is NULL, so COALESCE returns 'Unknown'. For alice_m, country is 'US', so COALESCE returns 'US' unchanged.

Multiple Fallbacks

COALESCE can chain more than two arguments. The database evaluates them left to right and returns the first non-NULL value:

For stream 7 (gina_null's stream), s.country is NULL and u.country is also NULL, so COALESCE falls through to 'Unknown'. For stream 1 (alice_m's stream), s.country is 'US', so COALESCE returns 'US' immediately without checking the other arguments.

This cascading pattern is useful when data can come from multiple sources with different completeness levels.

COALESCE in Calculations

NULL propagates through arithmetic. Any expression involving NULL produces NULL:

COALESCE lets you substitute a safe default before the calculation:

Without COALESCE, amount - refund_amount would produce NULL for every payment that has no refund, because 9.99 - NULL is NULL. With COALESCE(refund_amount, 0), those rows compute 9.99 - 0 = 9.99 instead.

COALESCE vs. Dedicated Functions

PostgreSQL, MySQL, and SQL Server each have their own NULL-replacement functions in addition to COALESCE:

COALESCE is the safest choice because it's standard SQL and works everywhere. The database-specific alternatives exist for historical reasons but offer no advantages in modern code.

NULLIF takes two arguments and returns NULL if they're equal. If they're not equal, it returns the first argument.

That may sound like an odd function, but it solves a specific and common problem.

Avoiding Division by Zero

Division by zero throws an error in most databases. NULLIF turns a zero denominator into NULL, which makes the division produce NULL instead of crashing:

A user with no completed payments (or all zero-amount payments) would cause a division-by-zero error. NULLIF prevents this:

If SUM(amount) is 0, NULLIF(SUM(amount), 0) returns NULL, and dividing by NULL produces NULL instead of an error. The result for that user shows as NULL rather than crashing the entire query.

Combining COALESCE and NULLIF

You can wrap the division in COALESCE to turn the NULL result into a default value:

Now users with zero total payments get a refund rate of 0 instead of NULL.

Turning Empty Strings into NULL

Some applications store empty strings where NULL would be more appropriate. NULLIF can normalize these:

NULLIF(country, '') returns NULL when country is an empty string, then COALESCE replaces that NULL with 'Unknown'. If country has an actual value, NULLIF returns it unchanged and COALESCE passes it through.

Aggregate functions handle NULL in ways that are logical but often surprising.

COUNT(*) vs. COUNT(column)

COUNT(*) counts all rows, regardless of NULL values. COUNT(column) counts only rows where the column is not NULL:

With our sample data: COUNT(*) returns 8, COUNT(country) returns 7 (gina_null has NULL country), and COUNT(timezone) returns 5 (three users have NULL timezone).

COUNT(DISTINCT column) also ignores NULLs:

This returns 4 (US, GB, KR, JP), not 5, because NULL is not counted as a distinct value.

SUM, AVG, MIN, MAX

These functions skip NULL values entirely. They don't treat NULL as zero. They pretend the row doesn't exist for the purpose of the calculation:

Only three rows have non-NULL refund_amount values: 9.99, 5.00, and 2.50. SUM gives 17.49. AVG gives 17.49 / 3 = 5.83. COUNT gives 3. The other seven rows are ignored.

The distinction between "NULL means skip" and "NULL means zero" is critical for AVG. If NULLs were treated as zeros, the average would be 17.49 / 10 = 1.749, which would be misleading. The actual average refund amount, among payments that had refunds, is 5.83.

If you do want NULL to act as zero in a SUM or AVG, use COALESCE:

This treats non-refunded payments as having a refund of 0, giving an average of 17.49 / 10 = 1.749.

Empty Groups and NULL

When an aggregate function operates on zero rows (an empty group), the behavior differs:

COUNT always returns a number. The other aggregates return NULL when there are no rows to aggregate:

No rows match user_id = 999, so SUM returns NULL, not 0. If you need 0 in this case:

When you ORDER BY a column that contains NULLs, the database has to decide where to put them. The SQL standard leaves this as implementation-defined, so behavior varies.

Default Behavior

PostgreSQL treats NULLs as larger than any non-NULL value. This means NULLs sort to the end in ASC order and to the beginning in DESC order. MySQL and SQL Server treat NULLs as smaller than any non-NULL value, producing the opposite placement.

In PostgreSQL, gina_null (NULL country) appears last. In MySQL, gina_null appears first.

NULLS FIRST and NULLS LAST

PostgreSQL and standard SQL let you control NULL placement explicitly:

This is useful when NULL has a specific meaning in your context. If NULL country means "international" and you want those users at the top:

Dialect Differences

In MySQL and SQL Server, you can simulate NULLS LAST for ASC ordering with:

The CASE expression sorts NULLs into group 1 and non-NULLs into group 0, pushing NULLs to the bottom.

NULL values in join columns never match each other. A join condition like ON a.col = b.col produces UNKNOWN when either side is NULL, and UNKNOWN rows are excluded from the join result.

This query joins users to their streams but only where the countries match. Stream 7 belongs to gina_null, who has NULL country, and the stream itself also has NULL country. You might expect these NULLs to match, but they don't. NULL = NULL produces UNKNOWN, so this stream is excluded from the result.

To include NULL-to-NULL matches in a join, use IS NOT DISTINCT FROM:

Now gina_null's stream is included because NULL IS NOT DISTINCT FROM NULL is TRUE.

LEFT JOIN and NULLs

LEFT JOIN preserves all rows from the left table, filling in NULLs for the right table's columns when there's no match. This creates a different kind of NULL: one that means "no matching row exists" rather than "the value is unknown."

If a user has no streams, stream_id is NULL in the result. This is a common pattern for finding rows without matches:

The WHERE clause filters to rows where the join found no match, indicated by the NULL stream_id.

SQL has a special rule for DISTINCT and GROUP BY: NULLs are treated as equal to each other for grouping and deduplication purposes. This is inconsistent with how NULLs behave in comparisons (where NULL = NULL is UNKNOWN), but it's the defined behavior in the SQL standard.

DISTINCT

This returns US, GB, KR, JP, and one NULL row. Even if multiple users had NULL country, DISTINCT would collapse them into a single NULL.

GROUP BY

All users with NULL country end up in a single group. With our data, gina_null is the only one, so the NULL group has a count of 1. If five users had NULL country, they'd all be in one group with a count of 5.

This grouping behavior is practical. Without it, GROUP BY couldn't handle nullable columns at all, because each NULL would be incomparable to every other NULL and you'd never be able to form a group.