Using COALESCE in SQL Select Query

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.


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.

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