Coalesce Function returns the first null value. Let's Understand with an example.
Consider the Employees Table below. Not all employees have their First, and Last Names filled. Some of the employees have First name missing, and some of them last name.
|
Emp_ID |
Emp_Name |
Last_Name |
Address |
|
2 |
lalita |
NULL |
Noida |
|
1 |
Ankit |
Shukla |
Noida |
|
3 |
Ankita |
Panday |
Delhi |
|
4 |
Anku |
Shukla |
US |
|
5 |
XYZ |
NULL |
UK |
Now, let's write a query that returns the Name of the Employee. If an employee, has all the columns filled - Emp_Name and Last Names, then we only want the Last_Name.
If the Last_Name is NULL, and if Emp_name column are filled then, we only want the Last Name. For example, Employee row with Epm_Id = 1, has the Last_Name filled, so we want to retrieve his Last_NAme "Shukla". Employee row with Id = 2, has Emp_name filled, but the Last name is missing. Here, we want to retrieve his Emp_name "Lalita". In short, The output of the query should be as shown below.
Query:- select emp_id, coalesce(last_name,Emp_name)as Name from tblemployee
|
2 |
lalita |
|
1 |
Shukla |
|
3 |
Panday |
|
4 |
Shukla |
|
5 |
XYZ |
