SQL Mastery
Intermediate 14 min read SQL Mastery

NULLs and Coalescing

Design for missingness, don't stumble into it

NULL is not a value — it is the absence of a value. Every comparison, aggregate and join inherits three-valued logic unless you design for it explicitly.

NULLCOALESCEIS NULLthree-valued logicmissing data
Prerequisites: SQL Query Fundamentals
Before we start

What you will learn

  • Explain three-valued logic (TRUE / FALSE / UNKNOWN)
  • Use IS NULL, COALESCE and NULLIF correctly
  • Predict how NULLs behave in aggregates, joins and ORDER BY
Introduction

What is this?

NULL denotes the absence of a value. Comparisons with NULL evaluate to UNKNOWN, not TRUE/FALSE, which filters rows out of WHERE, pads equality untested in joins, and skips rows in sums. SQL gives you IS NULL, COALESCE and NULLIF to handle missingness explicitly.

Motivation

Why do you need it?

  • SUM() silently skips NULLs — a column half-NULL produces an undercount that looks legitimate.
  • A join ON a = b pairs 'missing A' with nothing — missing keys vanish.
  • Missing-money handling is the #1 source of 'why is the total different' in data teams.
Think about it

Mental model

Imagine

An unanswered survey question

Think of

NULL

A blank isn't 'no answer' and isn't '0' — it's 'unknown'. Asking 'did 5 people answer 0?' gets a different answer than 'did 5 people skip it?'. SUM ignores blanks; COUNT(*) doesn't; the difference is frequently surprising.

Mechanics

How it works

  • Three-valued logic: `NULL = NULL` is UNKNOWN; only `IS NULL`/`IS NOT NULL` test absence.
  • Aggregates: SUM/AVG ignore NULLs; COUNT(*) counts all rows, COUNT(col) counts non-NULL.
  • COALESCE(a, b, c) returns the first non-NULL; NULLIF(a, b) returns NULL when a = b.
  • ORDER BY puts NULLs first or last depending on dialect (NULLS FIRST/LAST) — default differs.
  • Joins treat NULL keys as unmatched: on = NULL matches nothing, even in FULL JOIN.

How NULL propagates.

Comparison
Aggregate
Join key
COALESCE
Hands-on

Examples

NULL handling done explicitly

NULL handling done explicitly
SELECT
  COALESCE(channel, 'unknown') AS channel,
  SUM(COALESCE(amount, 0)) AS revenue  -- don't undercount
FROM sales
WHERE amount IS NOT NULL OR channel IS NULL  -- explicit intent
GROUP BY 1;

COALESCE fixes display and arithmetic; IS NOT NULL filters precisely; nothing is left to logic's default.

Real world

In production

  • A revenue model wraps every financial measure in COALESCE(x, 0) so SUM never undercounts.
  • A null-safety audit counts IS NULL per critical column and gates the pipeline when it spikes.
  • A join on optional customer keys uses COALESCE(key, '-') so orphans land in an audit bucket.
Guidelines

Best practices

  • Decide a NULL policy per dataset: is missing 0, 'unknown', or invalid? Encode it.
  • COALESCE at the boundary (read or write), not scattered through every downstream query.
  • Test joining on nullable columns: add COALESCE to the key or a NOT NULL guard.
  • Document NULL semantics in the catalog: 'amount NULL means cancelled, never 0'.
Watch out

Common mistakes

  • `WHERE amount != 0` silently dropping NULL rows (UNKNOWN not TRUE).
  • SUM over a nullable column underreporting revenue with no signal.
  • Joining two nullable key columns and 'losing' legitimate matches.
  • Treating COALESCE(x, -1) as data instead of a stand-in sentinel that leaks downstream.
Root cause analysis

Common issues

Error
Revenue report says 0 for a region with data
Root cause

Amount column NULL-rich; SUM skipped them and COALESCE wasn't applied

Fix

COALESCE(amount, 0) at the model boundary and add a null-rate assertion.

Error
JOIN silently lost customers
Root cause

Customer key nullable with NULLs for anonymous visits

Fix

Store a stable fallback key ('anonymous-<id>') or bucket NULLs explicitly to an audit group.

When things break

Debugging tips

  • When totals differ, COUNT(*) vs COUNT(x) and COUNT(*) - COUNT(x) = rows with NULL x.
  • Add `WHERE x IS NULL` alone to see the missing population before deciding a policy.
  • Use NULLIF to detect glaring stand-ins: a column of -1s masks 'did not use COALESCE'.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

What is the result of SELECT NULL = NULL;

2

SUM(amount) counts NULL amounts as zero.

Go deeper

Official documentation

PostgreSQL NULL handling
docs
SQLite NULL semantics
docs