Explore SQL Coalesce function in SQL Server

This article explores the coalesce function in SQL Server that SQL uses to manipulate strings with NULLs in SQL Server.

Data manipulation is generating or manipulating existing data to be displayed or used by the business. Handling NULL values is essential in preparing the required data. Null indicates that a data value does not exist in the database using Structured Query Language.

SQL Coalesce function in SQL Server

The SQL Coalesce function in SQL Server helps handle the NULL values. The process always returns the first non-null value from the supplied list. The Coalesce function is the replacement of the SQL Case statement as well.

Syntax

COALESCE ( expression [ 1…n ] )

There are some properties for the supplied expressions, as specified below.

The expressions data type should be the same.

It evaluates the integer data first, followed by the character expressions.

Examples:

SELECT COALESCE (NULL,'X','Y')
SELECT COALESCE (NULL,100,20,30,40)
SELECT COALESCE (NULL,NULL,20,NULL,NULL)
SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Rajendra')
SELECT COALESCE (NULL,NULL,NULL,NULL,1,'Rajendra')

In the case of the different data types of expressions, you get the data type conversion error:

SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Rajendra',1)

However, it returns the highest precedence data type if it can convert the data types.

SELECT COALESCE(1,0,9.01)

SQL Coalesce in a string concatenation operation

For the string concatenation, we can also use the plus (+) operator. The plus operator returns the NILL if It has any NULL value in the input expressions, as shown below.

Create table #person
(
       FirstName varchar(20),
       MiddleName varchar(10),
       LastName varchar(20)
)
Insert into #person values ('Rajendra','','Gupta')
Insert into #person values ('Rajendra','K','Gupta')
Insert into #person values ('Rajendra','','')
Insert into #person values ('Rajendra',NULL,NULL)


SELECT firstName +' '+MiddleName+' '+ LastName FullName FROM #person

The query gave NULL output for the last insert statement because we have NULLs in the middle and last name.

The SQL Coalesce function replaces any NULL values with a value ‘ ‘ (Char(13)-space). Therefore, let’s rewrite the query with the SQL COALESCE function.

SELECT COALESCE(firstName,'') +' '+COALESCE(MiddleName,'')
 +' '+ COALESCE(LastName,'')  FROM #person

This time we did not get any NULLs values because we used the SQL COALESCE function before concatenating strings.

SQL Coalesce function and user-defined function

The below t-SQL statement uses the SQL Coalesce function in a user-defined function. Here, the user-defined function uses the COALESCE function and returns the person's full name.

Create table person
(
       id intidentity(1,1),
       FirstName varchar(20),
       MiddleName varchar(10),
       LastName varchar(20)
)
Insert into person(firstname, MiddleName, LastName) values ('Rajendra','','Gupta')
Insert into person(firstname, MiddleName, LastName) values ('Rajendra','K','Gupta')
Insert into person(firstname, MiddleName, LastName) values ('Rajendra','','')
Insert into person(firstname, MiddleName, LastName) values ('Rajendra',NULL,NULL)


CREATE FUNCTION dbo.fn_Coalesce
(
  @FirstName NVARCHAR(50),@MiddleName NVARCHAR(50),@LastName NVARCHAR(50),@id int
)
RETURNS NVARCHAR(200)
AS 
BEGIN
  DECLARE @fullName NVARCHAR(200);
 
  SELECT @fullName = COALESCE(firstName,'') +' '+COALESCE(MiddleName,'')
 +' '+ COALESCE(LastName,'')   
    FROM person
       where id=@id
       
        
  RETURN (@fullName);
END
GO

SELECT dbo.fn_Coalesce ([FirstName], [MiddleName], [LastName],id) as 'Full Name'
FROM [Person] where id=1
GO

SQL COALESCE With Computed Columns

The computed columns in SQL Server can also use the SQL COALESCE function to avoid the NULL values that might give inaccurate query results.

CREATE TABLE dbo.Items  
(  
    item_id    INT   IDENTITY,  
    item_Name  VARCHAR(30) NOT NULL,
    Price_Per  DECIMAL(6,2)   NULL,  
    Tax_Rate   DECIMAL(4,2)   NULL,  
    Quantity   DECIMAL   NULL,  
    Flat_Price DECIMAL(6,2)   NULL  
);  
GO  
 
INSERT dbo.Items (item_Name, Price_Per, Tax_Rate, Quantity, Flat_Price)  
VALUES  
    ('Cricket Balls',1.00, 0.07, 6, NULL),  
    ('Wicket',10.00, 0.07, NULL, NULL),  
    ('Gloves',5.00, 0.07, 4, NULL),  
    ('Bat',20.00, 0.07, NULL, NULL),  
    ('Hockey Stick',30.00, 0.07, NULL, NULL), 
    ('Helmet',20.00, 0.07, NULL, NULL), 
    ('Seven Iron',20.00, 0.07, NULL, NULL),     
    ('Wax', NULL, NULL, NULL, 20.00)
GO  

SELECT item_id,  
      item_Name,
      COALESCE(Quantity,1) as 'Quantity',
      COALESCE(Price_Per * COALESCE(Quantity,1), Flat_Price) as 'Charge',
      COALESCE(Tax_Rate,0) as 'Tax Rate',
      COALESCE(Price_Per * COALESCE(Quantity,1), Flat_Price) * COALESCE(Tax_Rate,0) as 'Tax',
      
      CAST(COALESCE(Price_Per * COALESCE(Quantity,1), Flat_Price) 
      +(COALESCE(Price_Per * COALESCE(Quantity,1), Flat_Price) * COALESCE(Tax_Rate,0)) AS money) AS 'Total Charge'   
FROM dbo.Items;  
GO
  
DROP TABLE Items; 
GO

SQL COALESCE and CASE expression

As stated earlier, the SQL COALESCE function is a short form of SQL CASE statement. The below section shows how you can write the same query using the SQL CASE statement and SQL COALESCE statement.

CREATE  TABLE EmergencyContact  (
       empid int,
    firstname  VARCHAR(100) NOT NULL,
    lastname   VARCHAR(100) NOT NULL,
    relationship VARCHAR(100),
    homephone  VARCHAR(25),
    workphone  VARCHAR(25),
    cellphone  VARCHAR(25)
  );
  INSERT INTO EmergencyContact ( empid, firstname, lastname, relationship, homephone, workphone, cellphone )
VALUES ( 1,
         'Kusum',
         'Agarwal',
         'Spouse',
         NULL,
         '920.176.1456',
         '928.132.2967' ),
              ( 2,
         'Akshita',
         'Gupta',
         'Daughter',
         NULL,
         NULL,
         '982.132.2867' ),
              ( 3,
         'Shyam Sunder',
         'Gupta',
         'Father',
         NULL,
         NULL,
        NULL)


             SELECT
  firstname+''+lastname fullname,
   relationship,
  COALESCE(homephone, workphone, cellphone, 'NA') phone
FROM
  dbo.EmergencyContact

  SELECT
  firstname+''+lastname fullname,
   relationship,
  CASE
       WHEN homephone is NOT NULL Then homephone
       WHEN cellphone is NOT NULL Then cellphone
       WHEN workphone is NOT NULL Then workphone
   ELSE'NA'
   END
   EmergencyContactNumber
FROM
  dbo.EmergencyContact

Look at the XML execution plan of SQL Coalesce function; you can see it uses the CASE Statement internally.

SQL Coalesce function and pivoting

The below query shows the usage of COALEPSE for populating the pivot tables in SQL Server.

CREATE TABLE #STATE
(
CITY VARCHAR(50),
STATE VARCHAR(500))
 
INSERT INTO #STATE VALUES('Jaipur','Rajasthan'),('Gurugram','Haryana'),('Mumbai','Maharasthra'),('Bangalore','Karnataka')
 
DECLARE @col nvarchar(MAX);
SELECT @col = COALESCE(@col,'') +CITY + ',' 
FROM dbo.#STATE 

SELECT substring(@col,1,len(@col)-1)

SQL COALESCE and SQL ISNULL comparison

The COALESCE and ISNULL() functions are almost similar with few differences.

• Both functions evaluate expressions and return a non-null.

• Both functions output is NULL if all input values are NULL.

• Both functions accept data types: integer, char, date, etc.

• ISNULL is limited to 2 parameters, whereas coalesce takes multiple arguments.

• If all input arguments are NULL, Coalesce returns the error – At least one of the arguments to COALESCE must be an expression that is not the NULL constant

• The ISNULL uses the first value data type to return the value, while the COALESCE produces output based on the highest precedence of data type.

SET NOCOUNT ON;
SELECT COALESCE(NULL,100) as 'Query1'
SELECT ISNULL(NULL,200) as 'Query2'
SELECT COALESCE(NULL, NULL, 3) as 'Query3'
SELECT ISNULL(NULL, ISNULL(NULL,4)) as 'Query4'