SQLite 3 reference · Conditional and Type Functions

SQLite COALESCE() Function

Return the first argument that isn't NULL. The standard way to replace NULL with a default value.

SQLite 3

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

coalesce(X, Y, ...)

Parameter	Description
`X, Y, ...`	Two or more values, checked in order.

The first non-NULL value, or NULL.

A default for missing valuesTry it yourself

SELECT name, coalesce(email, 'no email') AS email
FROM customers
WHERE customer_id IN (4, 5);

Output

name | email
Linus Torvalds | [email protected]
Margaret Hamilton | no email

Several fallbacksTry it yourself

SELECT name, coalesce(city, country, '?') AS place
FROM customers
WHERE customer_id IN (6, 7);

Output

name | place
Tim Berners-Lee | Geneva
Barbara Liskov | USA

IFNULL() · Return the first argument if it isn't NULL, otherwise the second. A two-argument coalesce().
NULLIF() · Return NULL if two values are equal, otherwise the first value. Often used to avoid division by zero.
IIF() · Return one value if a condition is true and another if it's false: a short form of CASE WHEN … THEN … ELSE … END.
SQL NULL Values · tutorial