SQL NULL values: IS NULL, COALESCE and three-valued logic

#Why it matters

SQL NULL handling causes more wrong numbers than any other beginner topic, because NULL mistakes never raise an error: the query runs and the total is simply off. Interviewers know this, so "why does <> 'cancelled' miss rows" and "NOT IN vs NOT EXISTS" are standard screening questions for analyst and data engineer roles. In January 2021's taxi data, 98,352 trips (7.18%) have NULL passenger counts, enough to move any average you report.

#The concept

This lesson uses the taxi.duckdb database from What is SQL? Databases, tables and your first query.

#NULL means unknown

NULL is not zero, not an empty string and not the word "NULL". It is a marker that says "no value is known here". A trip with passenger_count NULL might have had one passenger or four; the meter did not record it.

One database breaks this rule: Oracle stores an empty string '' as NULL, so where name = '' never matches there. PostgreSQL, MySQL, SQL Server, SQLite and DuckDB treat '' as a real, known, empty value:

-- PostgreSQL
select '' is null as empty_is_null;

 empty_is_null
---------------
 f

#Three-valued logic

Any comparison with NULL gives a third truth value, UNKNOWN (shown as NULL):

WHERE keeps a row only when the condition is TRUE. UNKNOWN rows are dropped, exactly like FALSE rows. So where passenger_count <> 1 silently excludes every NULL row, because "is an unknown number different from 1?" is unknown.

CHECK constraints work the other way round: they reject a row only when the condition is FALSE, so NULL passes a check. That asymmetry surprises people when they design tables.

#Testing for NULL

is distinct from treats NULL as a value that equals NULL and differs from everything else. PostgreSQL, DuckDB, SQL Server 2022, Snowflake and BigQuery support it. MySQL uses its own operator <=> for NULL-safe equality.

#COALESCE and NULLIF

• coalesce(a, b, c) returns the first non-NULL argument. Use it for display defaults (coalesce(phone, 'not given')) and for treating missing numbers as zero when that is correct.

• nullif(a, b) returns NULL when a = b, otherwise a. Its main job is safe division: tip_amount / nullif(fare_amount, 0) returns NULL instead of failing when the fare is zero.

SQL Server also has isnull(a, b), MySQL ifnull(a, b), Oracle nvl(a, b): two-argument versions of coalesce. Prefer coalesce, which is standard and takes any number of arguments.

#NULL in arithmetic and text

Any arithmetic with NULL is NULL: 5 + null is NULL. String concatenation varies:

MySQL's concat returns NULL if any argument is NULL; concat_ws (with separator) skips NULLs everywhere.

#Aggregates skip NULLs

count(col), sum, avg, min and max ignore NULL values. count(*) counts rows, NULL or not. Two consequences:

• avg(passenger_count) is the average over trips where the count is known, not over all trips.

• sum of a column that is entirely NULL (or of zero rows) is NULL, not 0. Wrap it in coalesce(sum(x), 0) when a report needs a zero.

#NOT IN and a single NULL

x not in (1, 2, null) expands to x <> 1 and x <> 2 and x <> null. The last part is UNKNOWN for every x, so the whole condition is never TRUE and the query returns no rows at all. If the list comes from a subquery on a nullable column, one NULL anywhere in that column empties your result. not exists does not have this problem.

#NULLs in GROUP BY and UNIQUE

• group by puts all NULLs into one group, even though NULL = NULL is unknown. Grouping treats them as "not distinct".

• A unique constraint allows many NULLs in PostgreSQL, MySQL, SQLite and Oracle, because no NULL equals another. SQL Server allows only one. PostgreSQL 15 added unique nulls not distinct to allow at most one.

#Worked example: passengers who are not counted

#The filter that loses 98,352 rows

select count(*) as all_rows,
       count(*) filter (where passenger_count = 1)  as eq_one,
       count(*) filter (where passenger_count <> 1) as not_one,
       count(*) filter (where passenger_count is null) as is_null
from trips;

┌──────────┬────────┬─────────┬─────────┐
│ all_rows │ eq_one │ not_one │ is_null │
│  int64   │ int64  │  int64  │  int64  │
├──────────┼────────┼─────────┼─────────┤
│  1369765 │ 966233 │  305180 │   98352 │
└──────────┴────────┴─────────┴─────────┘

966,233 + 305,180 = 1,271,413, which is 98,352 short of 1,369,765. Those are exactly the NULL rows: neither "equal to 1" nor "not equal to 1". The NULL-safe version keeps them:

select count(*) filter (where passenger_count is distinct from 1) as distinct_from_one
from trips;

┌───────────────────┐
│ distinct_from_one │
│       int64       │
├───────────────────┤
│            403532 │
└───────────────────┘

305,180 + 98,352 = 403,532. Now the parts add up to the whole, which is the check you should run on every split.

#Average passengers three ways

select avg(passenger_count)                              as avg_ignoring_nulls,
       avg(coalesce(passenger_count, 0))                 as avg_nulls_as_zero,
       avg(passenger_count) filter (where passenger_count > 0) as avg_excluding_zero,
       count(passenger_count)                            as rows_counted
from trips;

┌────────────────────┬────────────────────┬────────────────────┬──────────────┐
│ avg_ignoring_nulls │ avg_nulls_as_zero  │ avg_excluding_zero │ rows_counted │
│       double       │       double       │       double       │    int64     │
├────────────────────┼────────────────────┼────────────────────┼──────────────┤
│ 1.4115082982476976 │ 1.3101590418794464 │ 1.4418162959844523 │      1271413 │
└────────────────────┴────────────────────┴────────────────────┴──────────────┘

Three defensible-looking numbers: 1.41, 1.31 and 1.44. Which is right?

• Treating NULL as 0 is wrong. A trip with an unrecorded count did not carry zero people. It drags the average down by 7%.

• Ignoring NULLs (the default) is right if the missing rows are a random sample of trips.

• Excluding zero is also worth doing: 26,726 trips record 0 passengers, which is a data entry default, not reality.

The one-sentence answer for a report: "Average passengers per trip is 1.44, computed over the 1,244,687 trips with a recorded count of at least 1; 98,352 trips have no count and 26,726 record zero."

#Are the NULL rows random?

Check before you assume. The NULL rows are not random at all:

select VendorID, count(*) as trips, avg(fare_amount) as avg_fare
from trips group by VendorID order by VendorID;

┌──────────┬────────┬────────────────────┐
│ VendorID │ trips  │      avg_fare      │
│  int64   │ int64  │       double       │
├──────────┼────────┼────────────────────┤
│        1 │ 410762 │ 11.145971657553105 │
│        2 │ 860651 │  10.95138125674634 │
│     NULL │  98352 │ 26.088590674311497 │
└──────────┴────────┴────────────────────┘

Trips with no vendor (and no passenger count, payment type or rate code) have an average fare more than twice as high. They are a different kind of trip, and the group by shows them as one NULL group. Dropping them because "NULLs are bad" would lower average revenue per trip noticeably. Keeping NULLs visible as their own group is often the most honest report:

select coalesce(cast(passenger_count as varchar), 'not recorded') as passengers,
       count(*) as trips
from trips group by all order by trips desc limit 4;

┌──────────────┬────────┐
│  passengers  │ trips  │
│   varchar    │ int64  │
├──────────────┼────────┤
│ 1            │ 966233 │
│ 2            │ 161670 │
│ not recorded │  98352 │
│ 3            │  43935 │
└──────────────┴────────┘

The cast is needed because coalesce must return one type: a number column cannot hold the text 'not recorded'.

#SUM of nothing is NULL

select sum(passenger_count) as s,
       sum(passenger_count) filter (where VendorID is null) as s_null_rows,
       coalesce(sum(passenger_count) filter (where VendorID is null), 0) as s_coalesced
from trips;

┌─────────┬─────────────┬─────────────┐
│    s    │ s_null_rows │ s_coalesced │
│ int128  │   int128    │   int128    │
├─────────┼─────────────┼─────────────┤
│ 1794610 │        NULL │           0 │
└─────────┴─────────────┴─────────────┘

A dashboard tile that shows "NULL" or a blank instead of 0 is this bug. coalesce fixes it at the edge.

#Division by zero with NULLIF

PostgreSQL stops the whole query on the first zero fare:

select tip_amount / fare_amount from trips where fare_amount = 0 and tip_amount > 0 limit 1;

ERROR:  division by zero

DuckDB does not fail but returns inf for floating-point columns, which then poisons an average. nullif handles both:

select tip_amount / nullif(fare_amount, 0) as ratio
from trips where fare_amount = 0 and tip_amount > 0 limit 1;

 ratio
-------
 
(1 row)

The ratio is NULL, so avg skips it.

#The NOT IN trap on real data

Which payment codes from the data dictionary never appear in January? Build a small lookup table first:

create or replace table payment_types as
select * from (values (1, 'Credit card'), (2, 'Cash'), (3, 'No charge'),
                      (4, 'Dispute'), (5, 'Unknown'), (6, 'Voided trip'))
         as v(payment_type, payment_name);

select payment_type, payment_name from payment_types
where payment_type not in (select payment_type from trips);

┌──────────────┬──────────────┐
│ payment_type │ payment_name │
│    int32     │   varchar    │
└──────────────┴──────────────┘
            0 rows

Zero rows, because trips.payment_type contains NULLs. The not exists version answers the question:

select payment_type, payment_name from payment_types p
where not exists (select 1 from trips t where t.payment_type = p.payment_type);

┌──────────────┬──────────────┐
│ payment_type │ payment_name │
│    int32     │   varchar    │
├──────────────┼──────────────┤
│            5 │ Unknown      │
│            6 │ Voided trip  │
└──────────────┴──────────────┘

Adding where payment_type is not null inside the not in subquery also returns codes 5 and 6, but you have to remember it every time. Use not exists.

#Many NULLs in a UNIQUE column

-- PostgreSQL 17
create temp table customers (email text unique);
insert into customers values (null), (null);          -- INSERT 0 2: both accepted

create temp table customers2 (email text unique nulls not distinct);
insert into customers2 values (null);
insert into customers2 values (null);

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "customers2_email_key"
DETAIL:  Key (email)=(null) already exists.

#When to replace NULLs and when to keep them

Replacing NULLs is a reporting decision, not a syntax one:

• Replace when the business meaning is clear: no discount recorded means 0 discount; a missing count of returns on a day with no returns means 0.

• Keep when the value is genuinely unknown: passenger counts, ages, incomes. Report how many are missing.

• Label for display: coalesce(city, 'Unknown') in a chart, never in the stored table.

• Never fill a numeric NULL with 0 or -1 in a table others will average.

#Common mistakes

• where status <> 'cancelled' to mean "not cancelled". Rows with NULL status vanish. Use status is distinct from 'cancelled' or add or status is null.

• not in with a subquery. One NULL in the subquery column and you get zero rows. Use not exists.

• coalesce(x, 0) inside avg. It changes the denominator and the answer. Decide whether unknown means zero before you write it.

• Reporting sum without coalesce. Empty groups show NULL, and a total row built on them shows NULL too.

• Expecting concat to behave the same everywhere. MySQL's returns NULL if any part is NULL. Use concat_ws or coalesce each part.

• Not checking whether NULLs are random. The NULL-vendor taxi trips have more than double the average fare. Profile NULL rows before dropping them.

#Interview angle

#Practice task

On taxi.duckdb:

1. Count trips whose RatecodeID is not 1 (standard rate), first with <>, then NULL-safe. Explain the gap.

2. Compute the average tip as a share of fare for card trips, using nullif to avoid zero fares, and report how many trips were excluded and why.

3. Show store_and_fwd_flag counts with NULLs labelled 'not recorded'.

4. Write one sentence for a manager on which average passenger count to use and why.

-- 1
select count(*) filter (where RatecodeID <> 1) as not_standard,
       count(*) filter (where RatecodeID is distinct from 1) as not_standard_null_safe
from trips;

-- 2
select avg(tip_amount / nullif(fare_amount, 0)) as avg_tip_share,
       count(*) filter (where fare_amount = 0) as zero_fare_trips_skipped
from trips
where payment_type = 1;

-- 3
select coalesce(store_and_fwd_flag, 'not recorded') as flag, count(*) as trips
from trips group by all order by trips desc;

#Further reading

• PostgreSQL: comparison functions and operators: is distinct from, is null and their exact rules.

• PostgreSQL: conditional expressions: coalesce, nullif, greatest and least.

• DuckDB: NULL values: how DuckDB treats NULL in each kind of expression.

• MySQL: working with NULL values: MySQL's <=> and its quirks.