SQL Reference ยท Scalar Functions
SQL COALESCE() function
Reference for SQL COALESCE(): return the first non-NULL argument, supply default values, chain fallbacks, type rules and its use inside aggregates.
COALESCE(a, b, c, ...) returns the first argument that is not NULL, or NULL if all of them are. It is the standard way to supply a default for missing data and to choose between several possible sources of a value.
It is part of the SQL standard and works in every major database, which makes it preferable to vendor-specific variants such as IFNULL, NVL and ISNULL. Arguments are evaluated left to right and evaluation stops at the first non-NULL value.
SELECT COALESCE(email, 'no email') FROM employees;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
COALESCE(expr1, expr2 [, expr3, ...])
| Part | Description |
|---|---|
expr1, expr2, ... |
Expressions of compatible types. At least two (SQLite) or one or more (others) must be given. |
Returns: the value of the first non-NULL argument, or NULL if all are NULL. It is equivalent to CASE WHEN expr1 IS NOT NULL THEN expr1 WHEN expr2 IS NOT NULL THEN expr2 ... END.
Dialect differences
| Database | Notes |
|---|---|
| SQLite | Supported, with at least two arguments. Types are not coerced: the result keeps the type of the argument chosen. |
| PostgreSQL | Arguments must resolve to a common type; COALESCE(1, 'a') is an error. |
| MySQL | Supported; result type is the common type of the arguments. |
| SQL Server | Result type follows data-type precedence (the highest-precedence argument), which can cause surprising conversions. Evaluates arguments only as needed, but subqueries may be run more than once. |
| Oracle | Supported; stops evaluating at the first non-NULL (short-circuit). |
Usage notes
- Empty string is not
NULL.COALESCE('', 'x')is'', except in Oracle, where the empty string isNULL. - Defaults on aggregates.
SUM,AVG,MINandMAXreturnNULLover no rows. Wrap them:COALESCE(SUM(x), 0). - Replacing before comparing.
COALESCE(discount, 0) > 5treats missing discounts as zero. Filtering on the expression prevents index use; considerdiscount > 5whenNULLshould not match. - Sorting.
ORDER BY COALESCE(rank, 9999)putsNULLs last in any database. - Concatenation.
first || ' ' || COALESCE(middle || ' ', '') || lastavoids aNULLswallowing the whole string, since||returnsNULLif an operand isNULL. - Type consistency. All arguments should be of the same kind; mixing numbers and text works in SQLite but fails in strict databases.
- Do not hide bad data. A default can mask a missing join or a typo. Use it where "unknown means this value" is a real business rule.
Related
- NULLIF(), IFNULL(), NVL() and ISNULL(), CASE, IS NULL
- Lessons: COALESCE and NULLIF, NULL