In MariaDB, 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, 'Red', 'Black');

Result:

Red

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

Black was also non-NULL, but it came after Red and so it wasn’t returned.

Database Example

Suppose we run the following query:

SELECT
    PetName,
    DOB AS "Date of Birth"
FROM Pets;

And we get the following result:

+---------+---------------+
| PetName | Date of Birth |
+---------+---------------+
| Fluffy  | 2020-11-20    |
| Fetch   | 2019-08-16    |
| Scratch | 2018-10-01    |
| Wag     | 2020-03-15    |
| Tweet   | 2020-11-28    |
| Fluffy  | 2020-09-17    |
| Bark    | NULL          |
| Meow    | NULL          |
+---------+---------------+

We can see that the last 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(DOB, 'None Supplied') AS "Date of Birth"
FROM Pets;

Result:

+---------+---------------+
| PetName | Date of Birth |
+---------+---------------+
| Fluffy  | 2020-11-20    |
| Fetch   | 2019-08-16    |
| Scratch | 2018-10-01    |
| Wag     | 2020-03-15    |
| Tweet   | 2020-11-28    |
| Fluffy  | 2020-09-17    |
| Bark    | None Supplied |
| Meow    | None Supplied |
+---------+---------------+

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

We get this:

10

When All Values are NULL

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

SELECT COALESCE( null, null );

Result:

NULL

COALESCE() vs IFNULL()

When used with two values, COALESCE() works the same as IFNULL:

SET @a = null, @b = 52;
SELECT 
    COALESCE(@a, @b), 
    IFNULL(@a, @b);

Result:

+------------------+----------------+
| COALESCE(@a, @b) | IFNULL(@a, @b) |
+------------------+----------------+
| 52               | 52             |
+------------------+----------------+

Invalid Argument Count

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

SELECT COALESCE();

Result:

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