COALESCE

Functions
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

Filter by Database
SQL COALESCE Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all Standard COALESCE. Also IFNULL(expr1, expr2) as a 2-arg shorthand.
PostgreSQL Native all Standard COALESCE. Accepts any number of arguments.
SQL Server Native all Standard COALESCE. Also ISNULL(check_expr, replacement) as a 2-arg shorthand (differs from COALESCE in type coercion).
Oracle Native all Standard COALESCE. Also NVL(expr1, expr2) for 2-arg and NVL2(expr1, not_null_val, null_val) for conditional 3-arg.
SQLite Native 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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

Standard COALESCE. Accepts any number of arguments.

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

SQL Server Native syntax

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 Native syntax

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 Native syntax

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

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