Coalesce function in sql server.

COALESCE function
The COALESCE function in SQL returns the first non-NULL expression among its arguments.
Syntax:
             COALESCE ("expression 1", "expressions 2", ...)

Here Consider the Employees1 Table below. Not all employees have their First, Midde and Last Names filled. Some of the employees has First name missing, some of them have Middle Name missing and some of them last name. let's see how to use coalesce function 

Now, let's write a query that returns the Name of the Employee. If an employee, has all the columns filled - EmpFName, EmpMName and EmpLName, then we only want the EmpFName.

If the EmpFNameis NULL, and if EmpMName and EmpLName are filled then, Coalesce function returns middle name because COALESCE function in 
SQL returns the first non-NULL expression among its arguments like below.

SELECT Empid, COALESCE(EmpFName, EmpMName, EmpLName) AS Name
FROM Employees1

Output will be:

We are passing EmpFName, EmpMNameand, EmpLName columns as parameters to the COALESCE() function. The COALESCE() function returns the first non null value from the 3 columns.like above  Empid =1 has all columns filled then coalesce function return EmpFName ,Empid =3 has filled EmpLName and EmpFName, EmpMNameand is NULL Coalesce function return EmpLName
Coalesce function is very usefull.