Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT COALESCE(email, 'no email') FROM employees;

Try it

SQL A default for missing values ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL A chain of fallbacks ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Making NULL count as zero in a calculation ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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 is NULL.
  • Defaults on aggregates. SUM, AVG, MIN and MAX return NULL over no rows. Wrap them: COALESCE(SUM(x), 0).
  • Replacing before comparing. COALESCE(discount, 0) > 5 treats missing discounts as zero. Filtering on the expression prevents index use; consider discount > 5 when NULL should not match.
  • Sorting. ORDER BY COALESCE(rank, 9999) puts NULLs last in any database.
  • Concatenation. first || ' ' || COALESCE(middle || ' ', '') || last avoids a NULL swallowing the whole string, since || returns NULL if an operand is NULL.
  • 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.