SQL · Guides · October 30, 2026 · 14 min read

NULL in SQL: IS NULL, COALESCE, NULL in joins, counts and comparisons

NULL means “no value here”: unknown, missing or not applicable. It is not zero, not an empty string and not false. Most NULL surprises come from one rule: any comparison with NULL gives NULL (unknown), not true or false, and WHERE keeps only rows where the condition is true. So x = NULL finds nothing, NOT IN with a NULL in the list finds nothing, and a NOT in front of a condition does not flip the NULL rows to the other side.

The practical toolkit is short:

• test for NULL with IS NULL / IS NOT NULL, never with =;
• replace NULL with a default using COALESCE;
• turn a value into NULL with NULLIF (mainly to avoid division by zero);
• remember that aggregates skip NULLs and COUNT(*) does not;
• in joins, NULL keys never match, and a LEFT JOIN creates new NULLs.

The examples run on the airline database from the Nomto SQL course: 33,121 flights in summer 2026, 53,000 loyalty members, 1.5 million app events. The SQL is PostgreSQL syntax, and every result was produced by running the query.

Where NULLs come from

Real tables have NULLs for different reasons, and the reason matters for how you treat them. In this database:

• flights.actual_departure and actual_arrival are NULL when the event has not happened yet;
• loyalty_members.birth_year is NULL when the member did not say;
• app_events.member_id is NULL when the visitor was not logged in;
• loyalty_tier_history.valid_to is NULL for the current row, which has no end yet.

Counting non-NULL values per status shows the first case clearly:

SELECT status,
       COUNT(*) AS flights,
       COUNT(actual_departure) AS dep,
       COUNT(actual_arrival) AS arr
FROM flights
GROUP BY status
ORDER BY flights DESC;

Departed flights have a departure time but no arrival yet. Scheduled ones have neither. And eight cancelled flights do have times, which is a data quirk you only see by counting NULLs.

Comparisons: why = NULL finds nothing

SELECT NULL = NULL AS eq,
       NULL <> 1 AS ne,
       NULL IS NULL AS isnull,
       1 + NULL AS plus,
       'a' || NULL AS concat_op,
       concat('a', NULL) AS concat_fn;

Even NULL = NULL is not true: two unknown values are not known to be equal. Arithmetic and the || operator pass NULL through as well; concat() is the exception and treats NULL as an empty string.

So this query returns zero rows, without any error:

SELECT COUNT(*) FROM flights WHERE actual_departure = NULL;

While this one finds the 16,348 flights that have not departed:

SELECT COUNT(*) FROM flights WHERE actual_departure IS NULL;

Three-valued logic: NOT does not catch NULLs

A condition in SQL can be true, false or unknown. WHERE keeps only true. NOT unknown is still unknown, so the NULL rows are dropped on both sides:

SELECT COUNT(*) FILTER (WHERE birth_year < 1980) AS before_1980,
       COUNT(*) FILTER (WHERE NOT (birth_year < 1980)) AS from_1980,
       COUNT(*) FILTER (WHERE birth_year IS NULL) AS unknown,
       COUNT(*) AS total
FROM loyalty_members;

A condition and its negation add up to 46,681, not 53,000. If you split a table into “matches” and “everything else” with WHERE cond and WHERE NOT cond, the NULLs end up in neither half. Add OR col IS NULL to the side where they belong.

The same thing happens with <>. WHERE status <> 'Arrived' would silently skip rows with a NULL status (this table has none, but many do).

IS DISTINCT FROM: a NULL-safe comparison

When you want NULL treated as an ordinary value, so that two NULLs count as equal, use IS DISTINCT FROM and IS NOT DISTINCT FROM:

SELECT NULL IS DISTINCT FROM NULL AS a,
       1 IS DISTINCT FROM NULL AS b,
       NULL IS NOT DISTINCT FROM NULL AS c;

These always return true or false, never NULL. They are handy for “did this value change?” checks between two snapshots, where a change from NULL to something must count as a change. PostgreSQL and DuckDB support them; MySQL uses the <=> operator instead.

NULL in aggregates

All aggregate functions except COUNT(*) ignore NULLs. On three values, one of them NULL:

SELECT AVG(x) AS avg_x, SUM(x) AS sum_x, COUNT(x) AS cnt_x, COUNT(*) AS cnt
FROM (VALUES (1), (NULL), (3)) t(x);

The average is 4 / 2 = 2, not 4 / 3. COUNT(x) counts non-NULL values, COUNT(*) counts rows. On the real data:

SELECT COUNT(*) AS events,
       COUNT(member_id) AS with_member,
       COUNT(*) - COUNT(member_id) AS anonymous
FROM app_events;

Most events come from visitors who were not logged in. COUNT(DISTINCT member_id) ignores NULLs too, so it returns 33,624 real members, and the anonymous visitors do not appear as an extra “member”.

Skipping NULLs is usually what you want, and replacing them with zero is usually wrong. Compare the average age of members with and without a COALESCE:

SELECT ROUND(AVG(2026 - birth_year), 1) AS avg_age_known,
       ROUND(AVG(COALESCE(2026 - birth_year, 0)), 1) AS avg_age_wrong
FROM loyalty_members;

The second column treats 6,319 members of unknown age as newborns. Replace NULL with zero only when NULL really means zero, as with “no sales on this day”.

SUM of no rows is NULL, not 0

SELECT SUM(amount) AS revenue,
       COALESCE(SUM(amount), 0) AS revenue_zero,
       COUNT(*) AS segments
FROM ticket_flights
WHERE amount > 100000;

No segment costs that much, so there is nothing to sum. COUNT returns 0 for no rows, but SUM, AVG, MIN and MAX return NULL. Wrap them in COALESCE(..., 0) when the result feeds a report or a calculation, so that “no revenue” shows as 0 and not as an empty cell.

COALESCE: the first non-NULL value

COALESCE(a, b, c) returns the first argument that is not NULL. A typical use is a fallback: the actual departure if it happened, otherwise the scheduled one.

SELECT flight_no, scheduled_departure, actual_departure,
       COALESCE(actual_departure, scheduled_departure) AS best_known_departure
FROM flights
WHERE flight_no = 'PG0405'
  AND scheduled_departure BETWEEN '2026-08-10' AND '2026-08-14'
ORDER BY scheduled_departure;

All arguments must be of compatible types. COALESCE(birth_year, 'unknown') mixes an integer with text: PostgreSQL rejects it, and DuckDB fails with “Could not convert string ‘unknown’ to INT32” as soon as it meets a NULL row. Cast first:

SELECT COALESCE(CAST(birth_year AS VARCHAR), 'unknown') AS birth_year,
       COUNT(*) AS members
FROM loyalty_members
WHERE birth_year IS NULL OR birth_year > 2007
GROUP BY 1
ORDER BY 1;

NULLIF: turning a value into NULL

NULLIF(a, b) returns NULL if a = b, otherwise a. Its best-known job is protecting a division: dividing by NULL gives NULL instead of an error.

SELECT aircraft_code,
       COUNT(*) AS seats,
       COUNT(*) FILTER (WHERE fare_conditions = 'Business') AS business,
       ROUND(COUNT(*) * 1.0
             / NULLIF(COUNT(*) FILTER (WHERE fare_conditions = 'Business'), 0), 1) AS seats_per_business
FROM seats
GROUP BY aircraft_code
ORDER BY seats DESC;

Without NULLIF, PostgreSQL stops the whole query with “division by zero” because of two small planes. (DuckDB does not raise an error here: without NULLIF it shows Infinity for CR2 and CN1, which then poisons any average or total built on top. So a query that runs in DuckDB can still fail on PostgreSQL, and give odd numbers in both. Write the NULLIF.)

The second job: cleaning up fake values that should have been NULL. Some systems store a placeholder such as 1900 or 0 when a value is unknown. This dataset has 148 members born in “1900”:

SELECT COUNT(*) FILTER (WHERE birth_year = 1900) AS placeholder_1900,
       ROUND(AVG(2026 - birth_year), 1) AS avg_age_raw,
       ROUND(AVG(2026 - NULLIF(birth_year, 1900)), 1) AS avg_age_clean
FROM loyalty_members;

A 126-year-old frequent flyer is not a real customer. NULLIF turns the placeholder into NULL, and AVG then skips it like any other missing value.

NULL in GROUP BY and ORDER BY

GROUP BY is one place where NULLs are treated as equal: all NULLs form a single group.

SELECT birth_year - birth_year % 10 AS decade, COUNT(*) AS members
FROM loyalty_members
GROUP BY decade
ORDER BY decade NULLS FIRST;

(birth_year - birth_year % 10 rounds down to the decade in any database. The more obvious birth_year / 10 * 10 works in PostgreSQL, where integer division drops the remainder, but not in DuckDB, where / returns a fraction and you would get 1946 back.)

Sorting is where PostgreSQL and DuckDB disagree. PostgreSQL treats NULL as larger than any value: NULLs come last in ascending order and first in descending order. DuckDB puts NULLs last in both directions by default. So this query:

SELECT member_id, birth_year
FROM loyalty_members
WHERE member_id <= 12
ORDER BY birth_year DESC;

shows members 4 and 8 at the bottom in DuckDB, as above, but at the top in PostgreSQL. A “top 10 youngest members” with LIMIT 10 would return two unknowns in PostgreSQL. Write NULLS FIRST or NULLS LAST explicitly whenever the column can be NULL; both databases accept it.

NULL in joins

NULL keys never match

A join condition is a comparison, so a NULL on either side is never equal to anything:

SELECT COUNT(*) AS events, COUNT(m.member_id) AS joined
FROM app_events e
LEFT JOIN loyalty_members m ON m.member_id = e.member_id;

The 912,625 anonymous events find no member. With an inner join they would vanish from the result altogether, which may be fine for “events per member” and badly wrong for “events per day”. If you really need two NULL keys to match, join with ON a.key IS NOT DISTINCT FROM b.key, but think twice: two anonymous visitors are not the same person.

A LEFT JOIN creates NULLs

When a left row has no match, the right-hand columns are filled with NULL. That is how you find “parents with no children”, and it is also why COUNT(*) after a LEFT JOIN can mislead:

SELECT a.aircraft_code, a.model,
       COUNT(*) AS count_star,
       COUNT(f.flight_id) AS flights
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
GROUP BY a.aircraft_code, a.model
ORDER BY flights
LIMIT 3;

The A320 has no flights, but the LEFT JOIN still produces one row for it, with NULLs on the flights side. COUNT(*) counts that row and says 1. COUNT(f.flight_id) counts non-NULL values and correctly says 0. After a LEFT JOIN, count a column from the right-hand table.

The WHERE trap

Filtering the right-hand table in WHERE throws away exactly the NULL rows the LEFT JOIN created:

SELECT a.aircraft_code, COUNT(f.flight_id) AS cancelled
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
WHERE f.status = 'Cancelled'
GROUP BY a.aircraft_code
ORDER BY cancelled;

Eight models, not nine. For the A320 row, f.status is NULL, NULL = 'Cancelled' is unknown, and WHERE drops it. Move the condition into ON:

SELECT a.aircraft_code, COUNT(f.flight_id) AS cancelled
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code AND f.status = 'Cancelled'
GROUP BY a.aircraft_code
ORDER BY cancelled;

Now the filter decides which flights are joined, and every model stays. The SQL joins guide and the date spine guide show the same trap in other settings.

The NOT IN trap

x NOT IN (a, b, c) means x <> a AND x <> b AND x <> c. If any element is NULL, one of those comparisons is unknown, and the whole condition can never be true:

SELECT 1 IN (1, NULL) AS in_hit,
       1 IN (2, NULL) AS in_miss,
       1 NOT IN (2, NULL) AS not_in_miss,
       1 NOT IN (2, 3) AS not_in_clean;

With a subquery the NULL is invisible, which makes it worse. Which loyalty members have no app events?

SELECT COUNT(*) AS members_without_events
FROM loyalty_members
WHERE member_id NOT IN (SELECT member_id FROM app_events);

The subquery returns hundreds of thousands of NULLs (the anonymous events), so the answer is zero. NOT EXISTS compares row by row and is not affected:

SELECT COUNT(*) AS members_without_events
FROM loyalty_members m
WHERE NOT EXISTS (
  SELECT 1 FROM app_events e WHERE e.member_id = m.member_id
);

Use NOT EXISTS (or a LEFT JOIN ... WHERE right.key IS NULL anti join) for “rows with no match”. If you keep NOT IN, add WHERE member_id IS NOT NULL inside the subquery. More subquery patterns are in the SQL subquery guide.

NULL in CASE

A CASE branch whose condition is NULL is skipped, so NULL rows fall through to ELSE. CASE WHEN birth_year < 1980 THEN 'older' ELSE 'younger' END puts every member with an unknown birth year among the “younger”. Put WHEN birth_year IS NULL THEN 'unknown' first. The CASE WHEN guide shows this with real counts.

Checklist

• Testing for missing values? IS NULL / IS NOT NULL, never = NULL.
• Splitting rows with cond and NOT cond? Decide where the NULLs go.
• Counting? COUNT(*) for rows, COUNT(col) for non-NULL values. After a LEFT JOIN, count a right-hand column.
• Averaging? NULLs are skipped. Do not COALESCE them to zero unless NULL really means zero.
• Summing something that might have no rows? COALESCE(SUM(...), 0).
• Dividing? NULLIF(denominator, 0).
• Sorting a nullable column? Say NULLS FIRST or NULLS LAST.
• LEFT JOIN with a filter on the right table? Put it in ON.
• Rows with no match? NOT EXISTS, not NOT IN.
• Placeholder values like 1900, 0 or ‘N/A’? Turn them into NULL with NULLIF before aggregating.

FAQ

Why does WHERE column = NULL return no rows?

Because comparing anything with NULL gives NULL (unknown), not true, and WHERE keeps only rows where the condition is true. Use WHERE column IS NULL.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. In app_events, they differ by 912,625: the events with no member.

What is the difference between COALESCE and NULLIF?

They are opposites. COALESCE(a, b) replaces a NULL with a value. NULLIF(a, b) replaces a value with NULL when a equals b, typically NULLIF(x, 0) before a division.

Is NULL equal to an empty string?

No. In PostgreSQL and DuckDB, '' is a real value of length zero, and '' IS NULL is false. Oracle is the exception: it treats an empty string as NULL.

How are NULLs sorted in SQL?

It depends on the database. PostgreSQL sorts NULLs last in ascending order and first in descending order; DuckDB puts them last in both. Write NULLS FIRST or NULLS LAST to get the same result everywhere that supports it.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文