Most major RDBMSs support the COALESCE() operator, which returns the first non-null value from its list of arguments.

COALESCE() is a SQL-standard feature (it’s included in the ISO/IEC 9075 specification).

Syntax

The syntax goes like this:

COALESCE (V1, V2, ..., Vn)

So, at least one argument is required, but multiple arguments can be (and usually are) provided.

COALESCE() is considered an n-adic operator. In other words, it is an operator that has a variable number of operands (i.e. n operands).

Example

Here’s a simple example to demonstrate:

SELECT COALESCE(null, 'Papaya', 'Salad');

Result:

Papaya

In this case, Papaya was the first non-null value, and so COALESCE() returned that value.

Salad was also a non-null value, but it came after Papaya and so it wasn’t returned.

COALESCE() vs CASE

COALESCE() is typically considered a syntactic shortcut for the CASE expression.

As such, the following statement:

COALESCE (V1, V2) 

is equivalent to:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

And the following statement:

COALESCE (V1, V2, ..., Vn)

(for n ≥ 3) Is equivalent to:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, ..., Vn) END

When All Values are Null

If all values are null, COALESCE() returns null:

SELECT COALESCE( null, null );

Result:

null

Depending on your RDBMS, the actual output for null values may be different. For example, when using psql (for PostgreSQL), the empty string is output by default whenever a null value is returned (although this can be changed). It’s the same with SQLite (and this can also be changed).

In SQL Server, if all arguments are null, then at least one of the null values must be a typed null. Therefore, the above example actually results in an error (because all null arguments are null constants).

Expressions

COALESCE() returns the current value of the first expression that initially doesn’t evaluate to null. Therefore, if we pass an expression like this:

SELECT COALESCE( null, 3 * 5 );

We get this:

15

Database Example

Suppose we run the following query:

SELECT
  name,
  salary
FROM employee;

And we get the following result:

 name  | salary 
-------+--------
 Elise | 100000
 Rohit |  50000
 Homer |   null

We can see that the last row has a null value in the DOB column.

If we wanted to replace the null value with another value, we could use COALESCE() as follows:

SELECT
  name,
  COALESCE(salary, 0) AS salary
FROM employee;

Result:

 name  | salary 
-------+--------
 Elise | 100000
 Rohit |  50000
 Homer |      0

In this case we replaced all null values with the integer 0.

Differences Between RDBMSs

In general, COALESCE() works pretty much the same across RDBMSs.

But there are some differences.

Data Types

We can do the following in MySQL, MariaDB, and SQLite:

SELECT
  name,
  COALESCE(salary, 'None') AS salary
FROM employee;

Result:

name	salary
-----   ------
Elise	100000
Rohit	50000
Homer	None

In this case, whenever the salary column contains a null value, the output is None.

But that query can cause problems in SQL Server, PostgreSQL, or Oracle Database.

When I run that query in SQL Server, PostgreSQL, and Oracle Database, I get an error due to the substitute value being the wrong data type.

Here’s what SQL Server returns when I run the above query:

Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value 'None' to data type int.

Here’s what PostgreSQL returns:

ERROR:  invalid input syntax for type integer: "None"
LINE 3:   COALESCE(salary, 'None') AS salary
                           ^

And here’s what Oracle Database returns:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

But if I use a numeric value, I don’t get the error:

SELECT
  name,
  COALESCE(salary, 0) AS salary
FROM employee;

Result:

 name  | salary 
-------+--------
 Elise | 100000
 Rohit |  50000
 Homer |      0

So this final example works across all six of the above RDBMSs.

Null Arguments

Another difference between how RDBMSs process COALESCE() is in how they deal with null arguments.

As mentioned, in SQL Server, if all arguments are null, then at least one of the null values must be a typed null. In other words, if all arguments to COALESCE() are the null constant, then we get an error. This isn’t the case with other RDBMSs, where all arguments can be the null constant, and the output will be null instead of an error.