SQL COALESCE Function

The SQL COALESCE function is used to return the first, not Null value from the series of expressions.

In this article, we will show you, How to write, or How to use SQL Server Coalesce with an example

Sql Server Coalesce Syntax

Before getting into the practical example, the basic syntax behind this SQL Server Coalesce is as shown below:

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

SQL COALESCE example

In this example, we will show you, How to use the 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

Below statement will return Apple as the Output because Coalesce function will return the first not Null value

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

Below statement will return Orange as the Output because Coalesce function will return the first not-Null value.

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

Sql Server COALESCE example 2

This example will show you 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

In this example, we will show you, How to write a Coalesce function on a table. For this demonstration, We are going to use the [Emp] table present in our [SQL Tutorial] Database.

--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 will 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, return the office number.
2. If an employee does not have an Office Phone number, but he/she has a Mobile phone then, return the Mobile phone number.
3. And, If our employee in Emp table does not have an Office Phone number, Mobile phone number but, he/she has a Home phone then, return the Home phone number.
4. If an employee has an Office, Mobile, and Home phone number, then 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

Thank You for Visiting Our Blog