COALESCE

COALESCE is a function that takes any number of arguments and returns the first one that is not NULL. If every argument turns out to be NULL, it returns NULL itself. It is one of the most frequently used functions in everyday SQL, because “missing data” is everywhere and you almost always need a sensible fallback for it.

COALESCE syntax

SQL

COALESCE(val1, val2, ..., valN)

COALESCE evaluates its arguments left to right and stops as soon as it finds one that isn’t NULL:

How COALESCE picks a value

SQL

SELECT COALESCE(NULL, NULL, 'third value', 'never reached'); -- 'third value'
SELECT COALESCE(NULL, NULL, NULL);                             -- NULL

Providing a fallback display value

A very common use case is displaying a friendlier value when a column is missing data — for example, showing a user’s nickname if they set one, otherwise falling back to their first name, and finally to a generic label if neither is available:

Fallback chain for a display name

SQL

SELECT
  id,
  COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

id | display_name
---+--------------
1  | Speedy
2  | Grace
3  | Anonymous

Here, user 1 had a nickname set, so that was used. User 2 had no nickname but did have a first_name. User 3 had neither, so the query falls all the way through to the literal string 'Anonymous'.

Other common uses

Substituting 0 for NULL before doing arithmetic: COALESCE(discount, 0)
Combining several optional address lines into one: COALESCE(address_line_2, '')
Picking the most specific available value from several candidate columns

The portable, ANSI-standard choice

Several databases have their own vendor-specific functions that do something similar to COALESCE for exactly two arguments: SQL Server has ISNULL(a, b) and MySQL has IFNULL(a, b). Both return b when a is NULL, otherwise they return a.

Function	Dialect	Number of arguments
COALESCE(a, b, ...)	ANSI standard — all major databases	Any number
ISNULL(a, b)	SQL Server	Exactly two
IFNULL(a, b)	MySQL / MariaDB	Exactly two

Prefer COALESCE for portability

COALESCE is part of the ANSI SQL standard and works identically across PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. ISNULL and IFNULL are vendor-specific, limited to two arguments, and in some cases behave subtly differently around data types. Unless you have a specific reason to use a vendor function, reach for COALESCE by default — your queries will run unchanged if you ever switch databases.