How can we apply COALESCE() function on a MySQL table’s data value?

MySQLMySQLi Database

If we want to apply COALESCE() function on a MySQL table’s data value then we need to use the name of the columns as the argument of this function. If there would be a NULL value in the first column then it will check for the next column and so on until it gets non-NULL value. We are using the data from ‘employee’ table, as follows, to demonstrate the above concept −

mysql> Select * from employee;
+----+------------+-------------+-----------+
| Id | First_Name | Middle_Name | Last_Name |
+----+------------+-------------+-----------+
| 1  | Advik      | NULL        | Jhamb     |
| 2  | Rahul      | Kumar       | Sharma    |
| 3  | Raman      | Singh       | Rajput    |
| 4  | Krishan    | Kumar       | Khurana   |
| 5  | Sachin     | Ramesh      | Tendulkar |
| 6  | NULL       | Kumar       | Gaurav    |
| 7  | NULL       | Singh       | Parmar    |
+----+------------+-------------+-----------+
7 rows in set (0.00 sec)

mysql> Select COALESCE(First_Name, Middle_Name,Last_Name)AS Name FROM Employee;
+---------+
| Name    |
+---------+
| Advik   |
| Rahul   |
| Raman   |
| Krishan |
| Sachin  |
| Kumar   |
| Singh   |
+---------+
7 rows in set (0.03 sec)

In the above example, there are three arguments to COALESCE() function and above query returns name from the First_Name, Middle_Name and Last_Name and its returns value of Middle_Name if First_Name have NULL. Then same for Last_Name, it returns Last_Name value if First_Name and Middle_Name have NULL. It returns NULL if all three First_Name, Middle_Name and Last_Name are NULL.

raja
Published on 20-Feb-2018 13:41:52
Advertisements