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