🧩NULL functions

Defaults without lying in storage

Null-handling functions let you present business-friendly values in SELECT lists and expressions while keeping the warehouse honest: COALESCE chooses the first non-NULL argument; NULLIF maps sentinel values to NULL so downstream math or constraints behave predictably.

SQL

COALESCE: first non-NULL wins

SELECT shipment_id,
       COALESCE(actual_delivery, promised_delivery, shipped_on) AS report_date
FROM shipments;

✓Illustrative result set

shipment_id	report_date
9001	2026-04-01
9002	2026-04-05

SQL

NULLIF: turn “bad denominator” into NULL

SELECT run_id,
       passed_reads::float / NULLIF(total_reads, 0) AS pass_rate
FROM qc_summary;

✓Illustrative result set

run_id	pass_rate
R1	0.982
R2	NULL

COALESCE(measurement_ng, 0) everywhere

Downstream averages and control charts treat imputed zeros as real measurements, control limits drift and rare-event detection misfires.

Separate “missing” from numeric zero in reports

Surface NULL (or a labeled sentinel dimension) in staging views; only coerce inside narrowly scoped aggregates where the statistic’s definition explicitly allows it.

🗺️Dialect cheat sheet

Same idea, different spellings: watch portability in shared analytics SQL.

ANSI / portable

COALESCE(a, b, c): variadic. NULLIF(a, b): returns NULL when a = b.

Vendor shorthands

SQL Server: ISNULL(a, b) (two arguments only). Oracle: NVL(a, b). MySQL / SQLite: IFNULL(a, b). Prefer COALESCE in portable layers.

⚡ Advanced / dialect note

SQL Server offers ISNULL(a,b) (two args only) while ANSI COALESCE accepts a list. Oracle's NVL is similar to two-arg ISNULL: know your dialect when reviewing short SQL snippets in drivers or ORM logs.

Types matter

COALESCE must pick a single result type; mixing incompatible types can force surprising implicit casts. Keep arguments homogenous or wrap with explicit CAST.

TL;DR

NULL-handling helpers: COALESCE(a,b,…) returns the first non-NULL argument; NULLIF(x,y) returns NULL when x = y (handy to avoid divide-by-zero). ISNULL/NVL are vendor-specific single-binary cousins. These functions do not change SQL's set semantics, they only shape scalar outputs inside expressions.