conditional
COALESCE
Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.
COALESCE(expr1, expr2, ..., exprN)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| IFNULL(a, b) | MySQL/SQLite two-arg shortcut equivalent to COALESCE(a, b) |
| NVL(a, b) | Oracle-style two-arg alias (not standard SQL) |
| ISNULL(a, b) | SQL Server two-arg equivalent |
Examples
SELECT COALESCE(nickname, first_name, 'anonymous') FROM users;First non-NULL of the three, otherwise the literal fallback
SELECT COALESCE(SUM(amount), 0) FROM payments WHERE user_id = 42;0 instead of NULL for users with no payments
SELECT COALESCE(NULL, NULL, NULL);Returns NULL — every argument was NULL
SELECT IFNULL(email, 'n/a') FROM users;MySQL/SQLite two-argument shortcut
Dialect notes / Gotcha
All arguments must be implicitly castable to a common type or the engine raises an error. Short-circuit means side-effecting expressions (subqueries, RANDOM()) may or may not run.
Related functions
NULLIF
Returns NULL if value1 equals value2, otherwise returns value1. Handy for converting sentinel values (empty strings, 0) into NULL, or safely guarding against division by zero.
CASE WHEN
SQL's portable if/else-if expression. Evaluates each WHEN clause in order and returns the first matching result; returns NULL from an unmatched CASE without ELSE.
GREATEST / LEAST
Return the largest (GREATEST) or smallest (LEAST) of the provided arguments row-by-row — not across rows, which is MIN/MAX. NULL handling differs by engine: Postgres ignores NULL args, MySQL returns NULL if any argument is NULL.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet