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 😉
