In MySQL, the COALESCE() operator returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

Syntax

The syntax goes like this:

COALESCE(value,...)

Example

Here’s a simple example to demonstrate:

SELECT COALESCE(null, 'Fish', 'Rabbit');

Result:

Fish

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

Rabbit was also a non-NULL value, but it came after Fish and so it wasn’t returned.

When All Values are NULL

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

SELECT COALESCE( null, null );

Result:

NULL

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, 2 * 10 );

We get this:

20

Database Example

Suppose we run the following query:

SELECT
  PetName,
  PetType
FROM Pets;

And we get the following result:

PetName	PetType
------- -------
Meow	Cat
Woof	Dog
Tweet	Bird
Awk	NULL
Moo	Cow
Omph	NULL

We can see that two rows have NULL values in the DOB column.

If we wanted to replace NULL with another value, we could change the query as follows:

SELECT
  PetName,
  COALESCE(PetType, 'Unknown') AS "PetType"
FROM Pets;

Result:

PetName	PetType
------- -------
Meow	Cat
Woof	Dog
Tweet	Bird
Awk	Unknown
Moo	Cow
Omph	Unknown

In this case we replaced all NULL values with the string Unknown.

It doesn’t need to be a string though. Here’s another example that replaces NULL values with an integer:

SELECT 
  EmployeeName,
  Salary,
  COALESCE(Salary, 0) AS "Salary (adjusted)"
FROM Employees;

Result:

EmployeeName	Salary	Salary (adjusted)
--------------  ------  -----------------
Homer Einstein	NULL	0
Bart Hawking	100000	100000

Invalid Argument Count

Using COALESCE() without any arguments results in an error:

SELECT COALESCE();

Result:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1