A Practical Use of the SQL COALESCE Function

This article talks about the practical use of the SQL COALESCE function regarding some professional life scenarios. It highlights the importance of correct and in-time use of this function to address the database-related problems.

Additionally, we will implement the particular steps required to solve the problems with the help of this function.

Prerequisites 

Before you prepare to review and implement the upcoming examples in this article, it is highly recommended to get familiar with the following issues:

• T-SQL Basics. Readers should be well aware of T-SQL scripting. Also, they need to write and run SQL queries against sample databases comfortably.

• Basics of COALESCE function. Readers need to be familiar with this field. If you need the information to study it, refer to the article SQL COALESCE Function Handling NULL Values Effectively.

Sample Database

Setup a sample database called CoalesceUseDB as follows:

-- Setup sample database
Create DATABASE CoalesceUseDB;
GO

Alternatively, you can run queries against the tempdb database if you prefer to do so.

Thoughts about Practical Use

We are going to review two practical use cases of COALESCE function. We should keep in mind that the main goal of this function is to return the first Non-Null value from the list of the inputs (parameters) being passed to it – aparameter could be a column as well.

One of the approaches to such scenarios is using a storage structure (table), which contains multiple columns. Only one of those columns needs to be populated to make meaningful information.

Let us now go through the practical uses.

Web Hosting Subscriptions Scenario

Here, we consider a web hosting services provider whose (paid) services are used by some customers. Customers can choose to pay monthly, quarterly, or annually – in any of these ways.

Now, we suppose that the customers have just paid at the beginning of October. Thus, we visualize a tabular structure from the database point of view as follows:

Build a Table to store orders

We need to build a table to store all the orders made by the customers via any of the available payment options against the sample database:

Use CoalesceUseDB

-- Create WebOrder table
CREATE TABLE [dbo].[WebOrder]
(
	[Id] INT NOT NULL, 
	[Customer] VARCHAR(40) NOT NULL, 
    [YearlyPayment] DECIMAL(10,2) NULL, 
    [QuarterlyPayment] DECIMAL(10,2) NULL, 
    [MonthlyPayment] DECIMAL(10,2) NULL, 
    [OrderDate] DATETIME2 NOT NULL, 
    CONSTRAINT [PK_WebOrder] PRIMARY KEY (Id) 
)

Populate the table as follows:

-- Populate WebOrder table
INSERT INTO [dbo].[WebOrder] ([Id], [Customer], [YearlyPayment], [QuarterlyPayment], [MonthlyPayment], [OrderDate]) VALUES (1, N'Asif', CAST(70.00 AS Decimal(10, 2)), NULL, NULL, N'2020-10-01 00:00:00')
INSERT INTO [dbo].[WebOrder] ([Id], [Customer], [YearlyPayment], [QuarterlyPayment], [MonthlyPayment], [OrderDate]) VALUES (2, N'Peter', NULL, CAST(35.00 AS Decimal(10, 2)), NULL, N'2020-10-01 00:00:00')
INSERT INTO [dbo].[WebOrder] ([Id], [Customer], [YearlyPayment], [QuarterlyPayment], [MonthlyPayment], [OrderDate]) VALUES (3, N'Sarah', NULL, NULL, CAST(6.00 AS Decimal(10, 2)), N'2020-10-01 00:00:00')

Quick check

Have a quick look at the table by running the following T-SQL script:

-- View WebOrder table
SELECT wo.Id,wo.Customer,wo.YearlyPayment,wo.QuarterlyPayment,wo.MonthlyPayment,wo.OrderDate 
FROM dbo.WebOrder wo

The output is:

Identify the Problem

All looks good, but there is a problem.

We want to look at all the customers’ payments, regardless if they made a monthly, yearly, or quarterly payment. There does not seem to be a way to join all these payments together by avoiding NULLs, especially if you work on a report containing all the customers’ orders and ignore whether they paid monthly, yearly, or quarterly.

Design the Solution

The solution is to use the COALESCE function. It will join all these payment modes together and exclude non-essential NULL values.

This can be easily achieved as follows:

--View all the web orders regardless of the payment mode
SELECT wo.Id,wo.Customer,COALESCE(wo.YearlyPayment,wo.QuarterlyPayment,wo.MonthlyPayment) AS Payment,wo.OrderDate 
FROM dbo.WebOrder wo

The output is:

Using SQL View to Improve Solution

We can improve this solution by turning the script into an SQL view and reusing it for analysis and reporting:

-- Create AllWebOrders view
CREATE VIEW 
AllWebOrders
AS
SELECT wo.Id,wo.Customer,COALESCE(wo.YearlyPayment,wo.QuarterlyPayment,wo.MonthlyPayment) AS Payment,FORMAT(wo.OrderDate,'dd-MMM-yyyy') AS OrderDate
FROM dbo.WebOrder wo

Run the view as follows:

--Run SQL View to see all the web orders
SELECT awo.Id,awo.Customer,awo.Payment,awo.OrderDate
FROM dbo.AllWebOrders awo

The results are:

Hint: you can build an SSRS report using the SQL view as an underlying data retrieval strategy.

Self-Referencing Organisations Scenario

This is a more complicated but more common life database-related scenario.

The simplest way to understand it is appealing to the hierarchical (parent-child) relationship. Here, we consider a table with all the employees’ records and their managers’ records. This table also stores each manager as an employee at the same table.

However, we won’t focus entirely on the employee-manager tabular relationship here.

Let us consider a parent-child hierarchy where each organization belongs to a master one. The master organization itself is stored as an organization in the same structure to create a self-referencing relationship.

The best way to understand this is to build the structure and see it yourself.

Build a Table to Store Master and Sub-organisations

Create and populate a SQL table in the sample database to store the master and its sub-organizations as follows:

-- Creating master sub organisation table (self-referencing table)
CREATE TABLE [dbo].[Organisation]
(
	[Id] INT NOT NULL , 
    [Name] VARCHAR(40) NULL, 
	[Articles] INT NULL,
    [MasterId] INT NULL, 
    CONSTRAINT [PK_Organisation] PRIMARY KEY ([Id])
);
GO

-- Inserting data into the Organisation table
-- Populate Organisation table
INSERT INTO [dbo].[Organisation] ([Id], [Name], [Articles], [MasterId]) VALUES (1,'CodingSight',10, NULL)
INSERT INTO [dbo].[Organisation] ([Id], [Name], [Articles],[MasterId]) VALUES (2, 'SQL Blog', 2,1)
INSERT INTO [dbo].[Organisation] ([Id], [Name], [Articles],[MasterId]) VALUES (3, 'SSRS Blog', 3,1)
INSERT INTO [dbo].[Organisation] ([Id], [Name], [Articles],[MasterId]) VALUES (4,'CodingSight 2',5, NULL)
INSERT INTO [dbo].[Organisation] ([Id], [Name], [Articles],[MasterId]) VALUES (5, 'SSAS Blog', 1,4)
INSERT INTO [dbo].[Organisation] ([Id], [Name], [Articles],[MasterId]) VALUES (6,'SSIS Blog', 2,4)

Quick check and Analysis

We can view the newly created table by running the following query:

-- View Organisation table
SELECT o.Id,o.Name,o.MasterId
FROM dbo.Organisation o

Then, we get the following result:

Thus, we can infer that the following master organizations are stored in the table:

1. CodingSight
2. CodingSight 2

If you look at the MasterId column, you can see that the master organizations have NULL MasterId. It is because they are master organizations.

The following organizations are under the CodingSight master organization. They have the MasterId pointing to the CodingSight organisation:

1. SQL Blog
2. SSRS Blog

The same is true for the following sub-organizations under CodingSight 2 Master organization:

1. SSAS Blog
2. SSIS Blog

Problem Statement

Suppose we need to develop a report of all articles published by these organizations, including their sub-organizations, but represented by the master organization.

In simple words, we need to build a report to show all the articles published by a master organization, including those articles published by its sub-organizations, but we can’t mention the sub-organizations.

Design the Solution

The COALESCE function can be very handy here since we have to encounter NULLs for the master organization, but putting master and sub-organization in the function won’t help.

For example, we try to sum up articles by wrapping their ids into the function as follows:

-- Getting total articles for each of the master and sub-organization without using COALESCE
SELECT O.Id,O.MasterId,SUM(O.Articles) as Total_Articles FROM dbo.Organisation O
GROUP BY O.MasterId,O.Id

The output is:

Now, let us improve the output by using the desired function as follows:

-- Getting total articles for each of the master and sub organizations using COALESCE
SELECT COALESCE(O.Id,O.MasterId) MasterOrSubId,O.Name,SUM(O.Articles) as Total_Articles FROM dbo.Organisation O
GROUP BY COALESCE(O.Id,O.MasterId),O.Name

The output is:

We have successfully merged master and sub-organization Ids to get the total number of articles published by these organizations.

The script has to be more complicated to get the desired results as we need to filter sub-organizations out without losing the count of their articles. That count should be assigned to their master organizations.

Write the following T-SQL script to achieve this:

-- Sum of all the articles published by the master organizations and their sub-organizations represented by the master organizations 
SELECT a.OrgId,o2.Name,a.Total_Articles FROM 
(SELECT COALESCE(O.MasterId,O.Id) AS OrgId,SUM(Articles) as Total_Articles FROM dbo.Organisation o
WHERE COALESCE(O.MasterId,O.Id) IN
(SELECT Id FROM dbo.Organisation where MasterId IS NULL)
GROUP BY COALESCE(O.MasterId,O.Id)) as a
INNER JOIN dbo.Organisation o2
on o2.Id=a.OrgId

The output is:

Congratulations! We have successfully learned the practical usage of the COALESCE function concerning some interesting real-time scenarios.

Things to do

Now that you can handle NULL values effectively and solve complex problems with NULL values, you need to be substituted as per business requirements. Let’s try the following things to improve your skills further:

1. Try to create and run a SQL view for the self-referencing organization scenario:
2. Refer to the SSRS Reports Development in Simple Terms article and create a report for the web hosting scenario.
3. Add more data to the WebOrder table by supplying different OrderDate values mentioned in the web hosting services scenario. Then, turn the SQL view into a stored procedure that accepts the OrderDate parameter.
4. Refer to the Creating Professional SSRS Report based on Stored Procedure article and build an order date-based report for the modified scenario discussed in the previous point.

Read Also

Top Answers to 5 Burning Questions on SQL COALESCE Function

Handling the NULL Values Effectively with the SQL COALESCE Function for Beginners