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

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 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 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 returns Orange as the Output. Because 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

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