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 Use Coalesce in SQL Server with example

SQL COALESCE Syntax

Before getting into the practical example, the basic syntax behind the Coalesce in SQL Server is as follows:

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

SQL COALESCE Function example

In this example we will show you, How to use the SQL Server Coalesce function on String data.

--Example for SQL COALESCE
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 COALESCE Function example 2

This example will show you the working functionality of SQL Server Coalesce function 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 COALESCE Function – Practical example

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

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

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 Phone number, return the office phone 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 SQL will return an office phone number.

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