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 an example

COALESCE in SQL 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 example

In this example, we will show you, How to use the Coalesce function in SQL Server 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;

COALESCE in SQL example 2

This example will show you the working functionality of Coalesce SQL 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

COALESCE in SQL 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 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 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 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