COALESCE function returns the first nonnull expression among its arguments. you can use the COALESCE function like as:
COALESCE ( expression1, expression2...expressionN)
If all arguments are NULL, COALESCE function returns NULL.
Example:
In the following example, a table tbl_employee includes four columns with employee details:Employee ID, EmpName, Address1 and Address2 and we need to select data about Employee id, Name and their address.We can use the COALESCE function to receive only the nonnull value found in the Address1 and Address2.
tbl_employee
Employee Id | EmpName | Address1 | Address2 |
1 | Ankur | NULL | Noida |
2 | David | Delhi | NULL |
3 | John | Kolkata | NULL |
5 | Mohan | NULL | Mumbai |
Now use the Query like this:
SELECT Employeeid,Empname,COALESCE(Address1,Address2) AS [Address] FROM tbl_Employee
see the output :
Employee Id | EmpName | Address |
1 | Ankur | Noida |
2 | David | Delhi |
3 | John | Kolkata |
5 | Mohan | Mumbai |