SQL COALESCE Function

The SQL Server Coalesce function is used to return the first not Null value from the series of expressions. Let us see how to use Coalesce with an example, and the basic syntax behind this function is:

COALESCE (expression1, expression2, ......, expressionN)

SQL Coalesce example

Use this Coalesce on String data. The first statement returns Apple as the Output because this function will return the first non empty value.

The second SQL Server statement returns Orange as the Output. Because the first one is Null, the function always returns the first not empty value.

The working functionality of Coalesce on Numerical values

Coalesce Practical example

How to write this SQL Server Coalesce function on a table with a practical example that you might see in real-time. For this demonstration, we use the [Emp] table.

The following screenshot shows you the data inside the Emp table. As you can see, it has 15 records.

In this example, we will use Coalesce to find out the contact number of each employee:

1. If an employee has an Office number, the function returns the office number.
2. If an employee does not have an Office number but he/she has a Mobile, this function display the Mobile.
3. And, If our employee in the Emp table does not have an Office or Mobile number, but he/she has a Home phone, it displays the Home number.
4. If an employee has an Office, Mobile, and Home, then it will return the first non-empty, i.e., office number.

SELECT [Id]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,COALESCE ([Office Phone],  [Mobile], [Home Phone]) AS PHONE
      ,[Office Phone]
      ,[Mobile]
      ,[Home Phone]
  FROM [Emp]