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 the SQL Server Coalesce function on String data.

--Example for COALESCE in SQL Server
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;

OUTPUT

ANALYSIS

The below statement returns Apple as the Output because Coalesce function will return the first not Null value

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

The below SQL Server statement returns Orange as the Output. Because the first one is Null, and the Coalesce function always returns the first not-Null value.

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

Coalesce example 2

The working functionality of Coalesce on Numerical values

--Example for SQL COALESCE
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;

OUTPUT

Sql Server Coalesce Practical example

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

--Example for COALESCE in SQL Server
USE [SQL Tutorial]
GO
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 SQL Server Coalesce function to find out the contact number of each employee:

1. If an employee has an Office number, the Coalesce function returns the office number.
2. If an employee does not have an Office Phone number, but he/she has a Mobile phone, this Coalesce function returns the Mobile phone number.
3. And, If our employee in the Emp table does not have an Office Ph number or Mobile phone number, but he/she has a Home phone, it returns the Home phone number.
4. If an employee has an Office, Mobile, and Home phone number, then the Coalesce function will return an office phone number.

--Example for SQL Server COALESCE
USE [SQL Tutorial]
GO
SELECT [Id]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,COALESCE ([Office Phone],  [Mobile], [Home Phone]) AS PHONE
      ,[Office Phone]
      ,[Mobile]
      ,[Home Phone]
  FROM [Emp]

OUTPUT