Problem
Often, we need to handle NULL values in our data, or compare several data elements and return a non-null value, or to evaluate a value for nullability and if the value is NULL return an alternative non-null value. In this tip we will explore several options for handling these situations using the SQL Server COALESCE function.
Solution
Microsoft provides functions and methodologies to evaluate and handle Null values including COALESCE and ISNULL functions and CASE statements. We will explore the COALESE function and compare it to the ISNULL function and the CASE statement, show examples and explain advantages and disadvantages of use cases.
What Is the COALESCE Function
The SQL function COALESCE() is a basic SQL function that can be used to evaluate a list of values and return the first non-NULL value.
For example, the following list would return ‘A’. (NULL, NULL, ‘A’, ‘B’, ‘1’, ‘2’)
Why use the COALESCE Function
The COALESCE function is used as an alternative to the ISNULL() function or the CASE statement. The advantage over the ISNULL() function is that ISNULL() only take two parameters and COALESCE() can take many parameters. The advantage over a CASE statement is that it is easier of writing and less code.
SQL Version Support
According to Microsoft, the Coalesce function is available in SQL Server (all supported versions), Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, Parallel Data Warehouse. Coalesce function is ANSI compatible so it can also be used in other ANSI compliant SQL platforms.
SQL Server Coalesce Usage and Examples
The Coalesce function require 2 or more parameters: COALESCE ( expression [ ,…n ] ) .
--Example
SELECT COALESCE(NULL,1);
Compare COALESCE and CASE
SQL COALESCE and the CASE statement are very compatible. According to Microsoft Documents "The COALESCE expression is a syntactic shortcut for the CASE expression." It goes on to say that the COALESCE expression is rewritten by the query optimizer as a CASE expression.
Example: This example presents 4 SELECT statements each comparing the equivalent use of Coalesce and the Case statement:
--1. COALESCE vs. CASE:
DECLARE @Str1 CHAR;
DECLARE @Str2 CHAR;
DECLARE @Str3 CHAR;
DECLARE @Str4 CHAR;
--1. All Values NULL
SELECT COALESCE(@Str1, @Str2) as 'Coalesce Results'
,CASE
WHEN @Str1 IS NOT NULL THEN @Str1
WHEN @Str2 IS NOT NULL THEN @Str2
END as 'Case Results';
--2. Used with a Default.
SELECT COALESCE(@Str1, @Str2,'MyDefault') as 'Coalesce Results'
,CASE
WHEN @Str1 IS NOT NULL THEN @Str1
WHEN @Str2 IS NOT NULL THEN @Str2
ELSE 'MyDefault'
END as 'Case Results';
--3. Last value set.
SET @Str4 = '4';
SELECT COALESCE(@Str1, @Str2, @Str3, @Str4) as 'Coalesce Results'
,CASE
WHEN @Str1 IS NOT NULL THEN @Str1
WHEN @Str2 IS NOT NULL THEN @Str2
WHEN @Str3 IS NOT NULL THEN @Str3
WHEN @Str4 IS NOT NULL THEN @Str4
END as 'Case Results';
--4. 2nd value set.
SET @Str2 = '2';
SELECT COALESCE(@Str1, @Str2, @Str3, @Str4) as 'Coalesce Results'
,CASE
WHEN @Str1 IS NOT NULL THEN @Str1
WHEN @Str2 IS NOT NULL THEN @Str2
WHEN @Str3 IS NOT NULL THEN @Str3
WHEN @Str4 IS NOT NULL THEN @Str4
ELSE 'MyDefault'
END as 'Case Results';
GO
Results: Both Coalesce and Case return the same results, the first non-null value or if all are NULL they return NULL.
Compare COEALESCE to ISNULL
Initially, it appears that Coalesce and ISNULL are similar. However, there are distinct differences that I will highlight. Below are similarities and differences between Coalesce and ISNULL and examples.
Below are similarities between Coalesce and ISNULL:
- Both can be used to evaluate a value and provide a default value if the original value is NULL!
- Both can return NULL is all values are NULL.
- Both can take different data types: integer, char, date, etc. Though, mixing data type may result in errors.
Below are several differences between Coalesce and NULL:
- The Coalesce function is ANSI compatible whereas ISNULL is not. Thus, Coalesce works in other SQL platforms like Oracle.
- ISNULL is limited to 2 parameters whereas Coalesce can take many parameters. Though you can nest ISNULL functions to get similar results.
- Coalesce returns a NULL value if all parameters have a NULL value and at least one is “Typed” NULL, else an error is returned. This is not the case with ISNULL.
- With ISNULL the datatype of the first value is used for the returned value which can lead to truncation, whereas Coalesce evaluates each data type and returns the data type with the highest precedence.
Example 1: Coalesce and IsNull Similarities:
--Example 1. COALESCE vs. ISNULL Similarities
SET NOCOUNT ON;
SELECT COALESCE(NULL,1) as 'Result1'
SELECT ISNULL(NULL,2) as 'Result2'
GO
--Compare with more than 3 parameters
SELECT COALESCE(NULL, NULL, 3) as 'Result3'
SELECT ISNULL(NULL, ISNULL(NULL,4)) as 'Result4' --nested ISNULL Functions
Results: Below are the results of a simple example comparing Coalesce and IsNull and of an example with 3 parameters.
Example 2: Coalesce and IsNull both return NULL. For Coalesce one parameter must be have a defined data type:
-- COALESCE vs. ISNULL Similarities
--Example 2. If all arguments are NULL and at least one argument is typed NULL, COALESCE returns NULL.
SELECT ISNULL(NULL, NULL) --returns NULL
GO
SELECT COALESCE(NULL, NULL) --Error Msg 4127
GO
DECLARE @c char(1) --unassigned value for @c but is typed char(1)
SELECT COALESCE(NULL, NULL, @c) --returns NULL
Results: This show that the first and last results both return NULLs but the 2nd result throws an error because unlike the 3 statement the neither parameter are a defined data type.
Example 3: Coalesce and IsNull with mixed Data Types.
--Example 3: Mixed Data Types
SET NOCOUNT ON;
SELECT ISNULL(1,'my string') AS '1. ISNULL'
SELECT ISNULL('my string', 2) AS '2. ISNULL'
SELECT COALESCE(3,'my string') AS '3. Coalesce'
SELECT COALESCE('my string', 4) AS '4. Coalesce Error'
Results: These results show that in some case you can mix data types. However, depending on the order of the data types and the data type precedence, you may get an error. The error shows that Coalesce evaluates each data type and returns the data type with the highest precedence. It is best to avoid mixing data types.
Example 4: Coalesce and IsNull – Parameter evaluation and truncation.
--Example 4: Parameter Evaluation and Truncation
DECLARE @c char(1) --since unassigned the value for @c is typed NULL
SELECT ISNULL(@c,'YES') as 'ISNULL' -- Truncates based on the first data type
,COALESCE(@c, 'YES') as 'Coalesce' --Uses the data type of the first non-Null parameter.
Results: The IsNull truncates the results based on the data type of the first parameter. However, Coalesce evaluate all parameters and returns the highest data type precedence, thus we do not get truncation of data.
SQL Coalesce for Data Validation
SQL COALESCE can be used to ensure validate data is returned by comparing values and returning a valid value conditionally or by returning a default value.
Example: This example validates that the BusinessEntityID and Customer Name are populated depending on the Sales being to a Store or to an individual Person.
SELECT TOP (1000) C.[CustomerID]
,C.[AccountNumber]
,COALESCE(S.BusinessEntityID, P.BusinessEntityID,'UNK') as 'BusinessEntityID'
,COALESCE(S.[Name],p.FirstName + ' ' + p.LastName,'UNKNOWN') as 'Customer'
,C.[StoreID]
,S.[Name]
,C.[PersonID]
,p.FirstName + ' ' + p.LastName as 'Person'
FROM [AdventureWorks].[Sales].[Customer] C
LEFT JOIN Person.Person P ON C.PersonID = p.BusinessEntityID
LEFT JOIN SALES.Store S ON C.[StoreID] = S.BusinessEntityID
Results: In these results if the Store ID and Name are not null they are returned in the BusinessEntityID and Customer columns, else if the Sales was to an individual person, the PersonID and Person name are returned in the BusinessEntityID and Customer columns.
SQL Coalesce for String Concatenation
SQL COALESCE can be used to build concatenated strings when some elements may be nullable.
Example: This example shows the use of COALESCE to concatenate name parts to a Full name. In this example the Middle name or Suffix may be NULL:
-- Concatenate Name part to a full name.
SELECT
[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[FirstName] + ' ' +COALESCE([MiddleName], '') +' ' + [LastName] + ' ' + COALESCE([Suffix],'') as 'FullName'
FROM [Person].[Person];
Results: The results below show a Full Name built from a concatenation of the name parts. Here we use Coalesce to handle NULL values for Middle Name and Suffix setting defaults to an empty string.
Using COALESCE to Pivot Data
SQL COALESCE can be used to handle initial Null values when pivoting multiple rows into single rows.
Example: In this example we will use ProductCategory and ProductSubcategory to show how to take multiple rows and Pivot them to one row per ProductCategory. The first query show ProductCategory joined to ProductSubcategory before the pivot. The second query uses a temp table and loops through each ProductCategory using COALESCE to start the concatenation process for pivoting the ProductSubcategory values to each ProductCategory.
--1. Show Category Name and associated Subcategories:
SELECT C.ProductCategoryID, C.Name as 'Cat Name', S.Name as 'Sub Name'
FROM [Production].[ProductCategory] C
INNER JOIN [Production].[ProductSubcategory] S ON S.ProductCategoryID = C.ProductCategoryID
GO
Results: The first partial result shows the initial unpivoted multi row association between Product Categories and Product Subcategories.
Example: Next, we Pivot the results to one row per Product Category concatenating the Subcategories.
--2. Build a temp table that show Category Name with the Subcategories Pivoted to the same row.
CREATE Table #Product_SubCategories (
ProductCategoryID int,
ProductCategory nvarchar(100),
Subcategories nvarchar(1000) NULL
)
--3. Insert initial Rows.
INSERT #Product_SubCategories (ProductCategoryID, ProductCategory)
SELECT C.ProductCategoryID, C.Name
FROM [Production].[ProductCategory] C
GO
--4.
DECLARE @ProductCategoryID INT = 1
DECLARE @ProductSubCategories VARCHAR(1000)
WHILE (@ProductCategoryID IS NOT NULL)
BEGIN
SELECT @ProductSubCategories = COALESCE(@ProductSubCategories,'') + S.Name + '; '
FROM [Production].[ProductSubcategory] S
WHERE S.ProductCategoryID = @ProductCategoryID
UPDATE #Product_SubCategories
SET Subcategories = @ProductSubCategories
FROM #Product_SubCategories P
WHERE P.ProductCategoryID = @ProductCategoryID
SET @ProductSubCategories = NULL
SET @ProductCategoryID = (SELECT ProductCategoryID
FROM #Product_SubCategories
WHERE ProductCategoryID = @ProductCategoryID + 1)
END
--5. Show Results
SELECT * FROM #Product_SubCategories ORDER BY 1;
--6. Clean up
DROP TABLE #Product_SubCategories;
GO
Results: Above we used a temporary table and a While loop to cycle through the Subcategories for each Product Category and concatenates them to a semi-colon delimited string and updating that to the Subcategories in the Temp table.
Compare COALESCE in a Scalar Function
Here we use Coalesce in a Scalar Function that takes string parts and combines them to a single string result.
Example: First we will create a Scalar Function named ufn_COALESCE_Concat that takes 4 string parts and concatenates them to a single string delimited by single spaces. Then we use the AdventureWorks Person table passing the First, Middle, Last name and suffix columns to return the Full name.
--1. Create a String Concatenation Function using Coalesce.
USE [AdventureWorks]
GO
CREATE OR ALTER FUNCTION [dbo].[ufn_COALESCE_Concat](@Stg1 NVARCHAR(50),@Stg2 NVARCHAR(50),@Stg3 NVARCHAR(50),@Stg4 NVARCHAR(50))
RETURNS VARCHAR(1000)
AS
BEGIN
--Returns a single space concatenated string!
RETURN (REPLACE(COALESCE(@Stg1, '') + ' ' +COALESCE(@Stg2, '') +' ' + COALESCE(@Stg3, '') + ' ' + COALESCE(@Stg4,''),' ',' ') )
END;
GO
--2. Test the Function call building a Full Name from Person table name parts.
SELECT [dbo].[ufn_COALESCE_Concat] ([FirstName], [MiddleName], [LastName], [Suffix]) as 'Full Name'
FROM [Person].[Person];
GO
Results: After creating the ufn_COALESCE_Concat function, the results of calling the function querying the Person table show the Full names for all AdventureWorks Person records.
SQL COALESCE With Computed Columns
SQL COALESCE can be used with computed columns by defining default values as to avoid dealing with Null values that may render your results invalid.
Example Step 1: In this example I use TempDB and create a new “Item” table. In the Item table, I insert several item rows and for each item I provide a Price, tax Rate, quantity, and Flat Price. But these are not required fields and may have NULL values.
USE Tempdb;
GO
SET NOCOUNT ON;
GO
--1. Create New Items Table
IF OBJECT_ID('dbo.Items') IS NOT NULL
DROP TABLE Items;
GO
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
--2. Insert Test Records
INSERT dbo.Items (item_Name, Price_Per, Tax_Rate, Quantity, Flat_Price)
VALUES
('Golf Balls',1.00, 0.07, 6, NULL),
('Basketball',10.00, 0.07, NULL, NULL),
('Puck',5.00, 0.07, 4, NULL),
('Bat',20.00, 0.07, NULL, NULL),
('Hockey Stick',30.00, 0.07, NULL, NULL),
('Goalie Stick',20.00, 0.07, NULL, NULL),
('Seven Iron',20.00, 0.07, NULL, NULL),
('Wash Car', NULL, NULL, NULL, 20.00),
('Wax Car', NULL, NULL, NULL, 30.00),
('Sweep Floor',NULL, NULL, NULL, 10.00),
('Wax Floor', NULL, NULL, NULL, 20.00),
('Sweep Floor',NULL, NULL, NULL, 10.00)
GO
Example Step 2: In the next step I query the table using Coalesce to provide default values when NULLs are present. Here I show the Quantity with a default of 1. I calculate the Charge and the Tax, and finally I calculate the ‘Total Charge’ which brings it all together, using Coalesce to handle NULL values.
--3. Select results using multiple Coalesce Compute columns examples
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',
--Finally bring it all together
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
--4. Clean Up
DROP TABLE Items;
GO
Results: Shows the results of the Calculated columns that used Coalesce to set defaults and handle Null values.
Wrap Up
In this tip I explained uses of the COALESCE function and provide several comparisons and example of its use. Coalesce is simpler than a CASE statement. It is very much like ISNULL which typically I see used more in code. However, coalesce does have some advantages, including: no truncation, it takes more than 2 parameters, and it is ANSI compliant!
Next Steps
- Go more in depth with – The Many Uses of Coalesce in SQL Server
- Read about – Deciding between COALESCE and ISNULL in SQL Server
- Read about data type precedence – Microsoft Docs
- Find other articles on Concatenation – Using SQL Server Concatenation Efficiently
