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. The basic syntax behind this Coalesce function is:

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

SQL Coalesce example

Use this 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.

SELECT COALESCE('Apple', 'Orange', 'Kiwi', 'Cherry') AS Result1;

SELECT COALESCE(NULL, 'Orange', 'Kiwi', 'Cherry') AS Result2;

SELECT COALESCE(NULL, NULL, 'Kiwi', NULL) AS Result3;

SELECT COALESCE(NULL, NULL, NULL, 'Cherry') AS Result4;

The working functionality of SQL Coalesce on Numerical values

SELECT COALESCE(10, 20, 30, 40, 50) AS Result1;

SELECT COALESCE(NULL, 40, 50, 60) AS Result2;

SELECT COALESCE(NULL, NULL, 4, NULL) AS Result3;

SELECT COALESCE(NULL, NULL, NULL, 95, 105) AS Result4;

Sql Server Coalesce Practical example

How to write this function on a table. For this demonstration, we use the [Emp] table.

SELECT [Id]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Office Phone]
      ,[Mobile]
      ,[Home Phone]
  FROM [Emp]

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]