SQL COALESCE Function

The SQL Coalesce function is used to return the first not Null value from the series of expressions. Let us see how to use SQL Server 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, and the function always returns the first not empty value.

The working functionality of SQL Coalesce on Numerical values

Coalesce Practical example

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

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

In this example, we will use Coalesce in sql server 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 display 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]