COALESCEFunctions
Buy Me A Coffee

Returns the first non-NULL value from a list of expressions.

Returns the first non-NULL argument from a list, evaluating left to right. Essential for handling NULLs in expressions, displaying defaults, and building robust queries over sparse data. More concise than nested IFNULL or CASE expressions.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all Standard COALESCE. Also IFNULL(expr1, expr2) as a 2-arg shorthand.
PostgreSQL ✓ Supported all Standard COALESCE. Accepts any number of arguments.
SQL Server ✓ Supported all Standard COALESCE. Also ISNULL(check_expr, replacement) as a 2-arg shorthand (differs from COALESCE in type coercion).
Oracle ✓ Supported all Standard COALESCE. Also NVL(expr1, expr2) for 2-arg and NVL2(expr1, not_null_val, null_val) for conditional 3-arg.
SQLite ✓ Supported all Standard COALESCE. Also IFNULL(x, y) as a 2-arg shorthand.

Details

COALESCE is SQL-standard. All engines also ship proprietary 2-argument shorthands (IFNULL, ISNULL, NVL) that predate the standard.

Standard Syntax

SELECT COALESCE(preferred_name, first_name, 'Unknown') FROM users;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

Standard COALESCE. Also IFNULL(expr1, expr2) as a 2-arg shorthand.

SELECT COALESCE(a, b, 'default') FROM tbl; SELECT IFNULL(a, 'default') FROM tbl;

PostgreSQL

Standard COALESCE. Accepts any number of arguments.

SELECT COALESCE(a, b, c, 'default') FROM tbl;

SQL Server

Standard COALESCE. Also ISNULL(check_expr, replacement) as a 2-arg shorthand (differs from COALESCE in type coercion).

SELECT COALESCE(a, b, 'default') FROM tbl; SELECT ISNULL(a, 'default') FROM tbl;

Oracle

Standard COALESCE. Also NVL(expr1, expr2) for 2-arg and NVL2(expr1, not_null_val, null_val) for conditional 3-arg.

SELECT COALESCE(a, b, 'default') FROM tbl; SELECT NVL(a, 'default') FROM tbl; SELECT NVL2(a, 'has value', 'is null') FROM tbl;

SQLite

Standard COALESCE. Also IFNULL(x, y) as a 2-arg shorthand.

SELECT COALESCE(a, b, 'default') FROM tbl;