The MySQL COALESCE()
function returns the first non-null value from a list of expressions that you passed as parameters to the function.
The list of expressions are separated by a comma:
SELECT COALESCE(`expression`, `expression`, `expression`);
Here’s an example of the COALESCE()
function in action:
SELECT COALESCE(null, "Joe", 27, false);
-- returns "Joe"
Because COALESCE()
returns the first non-null value, the boolean value false
can be returned by the function as 0
:
SELECT COALESCE(false, "Joe", 27);
-- returns 0
The function can also accept table column names as its parameters.
For example, suppose you have a table named students
with the following data:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 1 | Mark | Math | 7 | male |
| 2 | Natalia | Math | 8 | female |
| 3 | NULL | Math | NULL | male |
+----+---------+---------+-------+--------+
You can use the COALESCE()
function to query the table as shown below:
SELECT COALESCE(name, subject) FROM students;
The returned result set will be as follows:
+-------------------------+
| COALESCE(name, subject) |
+-------------------------+
| Mark |
| Natalia |
| Math |
+-------------------------+
Because the name
column in the third row is NULL
, the value of subject
column is returned for the third row (which is Math
)
And that’s how the COALESCE()
function works in MySQL database server 😉