Recipe

Replace NULL with 0 (or any default)

Short answer: COALESCE(col, 0) returns the first non-NULL of its arguments. It is the canonical, ANSI-portable way to substitute a default for NULL, and works on every engine. IFNULL (MySQL / SQLite) and ISNULL (SQL Server) are dialect-specific 2-argument shortcuts; use COALESCE for portability.

Canonical SQL #

sql

-- Portable.
SELECT COALESCE(amount, 0)               AS amount,
       COALESCE(name, '(unknown)')        AS name,
       COALESCE(a, b, c, 'default')       AS first_non_null
FROM   t;

-- Engine-specific 2-argument forms.
SELECT IFNULL(amount, 0)  FROM t;        -- MySQL / SQLite / DuckDB
SELECT ISNULL(amount, 0)  FROM t;        -- SQL Server (note: different name = different function)
SELECT NVL(amount, 0)     FROM t;        -- Oracle

Example #

Loading SQL editor...

Common patterns #

sql

-- Sum across columns where any might be NULL.
SELECT COALESCE(q1, 0) + COALESCE(q2, 0) + COALESCE(q3, 0) + COALESCE(q4, 0) AS year_total
FROM   quarterly;

-- "Use the override if set, else the default."
SELECT COALESCE(custom_price, list_price) AS effective_price FROM products;

-- Return a placeholder string for missing names.
SELECT COALESCE(first_name || ' ' || last_name, '(no name)') AS display_name FROM users;
-- ...but if just first_name is NULL, the whole concat is still NULL. Use CONCAT_WS instead:
SELECT CONCAT_WS(' ', first_name, last_name) AS display_name FROM users;

-- "Anything-but-NULL" trick — chain through preferences.
SELECT COALESCE(work_email, personal_email, '(no email on file)') FROM contacts;

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL	`COALESCE(col, 0)`	ANSI; works on every engine in this table
MySQL	`IFNULL(col, 0)`	`ISNULL(col)` here is a 1-arg null test, not a replacement
SQL Server	`ISNULL(col, 0)`	result type comes from the first argument, which can silently truncate the default
BigQuery	`IFNULL(col, 0)`	—
Snowflake	`IFNULL(col, 0)`	`NVL` is a synonym
Oracle	`NVL(col, 0)`	always evaluates both arguments; `NVL2` for an if/else form
SQLite	`IFNULL(col, 0)`	exactly two arguments; use `COALESCE` for more
DuckDB	`COALESCE(col, 0)`	`IFNULL` also supported

Common mistakes #

COALESCE evaluates lazily. Most engines stop at the first non-NULL, so expensive expressions in later positions are skipped. But not all do (some evaluate every argument). Don't rely on lazy evaluation for side effects.
Type mismatch: COALESCE(int_col, 'unknown') errors because the engine can't pick a common type. Cast explicitly.
COALESCE inside an aggregate: SUM(COALESCE(amount, 0)) and SUM(amount) give the same result on most engines, since SUM ignores nulls naturally. Only need COALESCE when the surrounding context cares about the value of the aggregate when there are no rows (which returns NULL, not 0).
Using ISNULL portably: ISNULL is a 2-arg "replace null" function on SQL Server but a 1-arg "is the value null" predicate on MySQL/Postgres. Use COALESCE to avoid the ambiguity.

Tip

**For empty-result aggregations, COALESCE belongs outside the aggregate, not inside.** SUM(amount) returns NULL when the table is empty (no rows to sum); COALESCE(SUM(amount), 0) makes it 0. SUM(COALESCE(amount, 0)) also returns 0 for empty input, but spends CPU per row replacing nulls inside the sum. The outside form is both clearer and slightly faster. Detail: COALESCE, IS NULL.