What is COALESCE() function?

It’s a kind of decision making function and it contains more than one expression. When we are executing this, it first checks for the first expression and moves forward if it returns NULL value. Otherwise it returns value of the first expression. If the value of the first expression is NULL, it goes further to process the second expression and so on…

Syntax:

COALESCE(Expression-1, Expression-2, Expression-3……)

Examples:

——————————————————————————–
USE [ADVENTUREWORKS2012]
GO
——————————————————————————–
— Ex 1: If AddressLine2 is NULL
——————————————————————————–
SELECT [AddressID],[AddressLine2],[AddressLine1],
COALESCE([AddressLine2],[AddressLine1]) AS [COALESCE]
FROM [Person].[Address]
WHERE [AddressID] = 1
——————————————————————————-
— Ex 2: If AddressLine2 is NOT NULL
——————————————————————————-
SELECT [AddressID],[AddressLine2],[AddressLine1],
COALESCE([AddressLine2],[AddressLine1]) AS [COALESCE]
FROM [Person].[Address]
WHERE [AddressID] = 12037
——————————————————————————

In Ex 1, AddressLine2 is NULL. So it displays AddressLine1.

In Ex 2, AddressLine2 is contains a value (NOT NULL). So it displays the same value.

Result set:

Coalesce

 Happy Reading :)

Leave a Reply

Your email address will not be published. Required fields are marked *