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

This article aims to help beginners understand the basics of the T-SQL COALESCE function and its application for handling NULL values. Additionally, the readers will get hands-on experience by implementing some simple examples of this function.

We’ll also highlight the importance of T-SQL functions in resolving database analytical problems.

About the SQL COALESCE Function

First, let’s try to understand this function from a SQL beginner’s point of view.

What is the SQL COALESCE Function?

It is a T-SQL function that accepts many inputs but returns the first input value that is NOT NULL.

What is the input?

An input (also called an argument) can be anything supplied to a function required by that function to do its job.

e.g., We have a function called SUM(), which adds any two numbers X and Y. These numbers are inputs or arguments of the function.

An input or argument can be an expression.

What is an expression?

An expression can be a constant, a variable, or a column that ultimately returns a single data value.

However, we are particularly focused on the columns from the database perspective, which may or may not contain values.

What is a Null Value?

Any value yet to be assigned to a table’s column is a NULL value. In other words, NULL means an uninitialized value of a column in the table.

What is a NOT NULL Value?

It is the opposite of the NULL value. If the NULL value is an unassigned or uninitialized column value, the NOT NULL value is an assigned or the initialized column value.

e.g., We have two columns, ExamId and ExamResult, in a table.

A user sets ExamId=1, but does not supply any value to the ExamResult column. Then ExamResult will have NULL in it. Alternatively, ExamId has NOT NULL value because it is 1.

How Does COALESCE function work?

It is a function that shows the first NOT NULL value of the column in the list of the columns passed on to this function. However, we can pass any set of expressions, which can be a list of the table columns or a simple set of numbers (constants) or variables to this function.

Syntax

The SQL syntax of COALESCE function (as per Microsoft documentation) is as follows:

COALESCE ( expression [ ,...n ] )   

We have already defined an expression so that it can accept any number of the same type expressions.

Compatibility

According to Microsoft documentation, this function is compatible with many Microsoft SQL technologies, including the following:

1. All supported versions of SQL Server.
2. Azure SQL Database (SQL Server cloud version).

Note that I have deliberately excluded a few other SQL Server technologies to keep the context simple.

Simple Examples of the COALESCE Function

Let’s set up a sample database where we can run our examples. Create a new database called ‘CoalesceDB’ to run your examples against it. Use the following code:

-- Create sample database
Create DATABASE CoalesceDB;
GO

‘Number with a NULL’ Example

If we pass a number and a NULL value to the COALESCE function in any order, it will return the number because it is the first NOT NULL value.

Use the T-SQL script against the sample database as follows:

-- Use the sample database
USE CoalesceDB

-- Simple COALESCE example with number and NULL inputs (arguments) 
SELECT COALESCE(NULL,1) as COALESCE_Result

You should get the following results:

If we reverse the order of the arguments in such a way that number 1 comes first, and NULL becomes the second argument, we still get the same result:

-- Simple COALESCE example with Number and NULL while number being first input (arguments) 
SELECT COALESCE(1,NULL) as COALESCE_Result_Number_First

‘String with a NULL’ Example

Here we will pass a string (character type data) and a NULL to this function to see the results. Run the following script:

-- Simple COALESCE example with string and NULL inputs (arguments) 
SELECT COALESCE(NULL,'Second input') as COALESCE_Result

The results are below:

Similarly, reversing the order will get us the same results as in the previous example.

‘Two Numbers and a NULL’ Example

Let’s see the COALESCE function behavior if we supply the three inputs (arguments), including two numbers and a NULL value, to this function.

Run the following script for the example with two numbers and one NULL value:

-- Simple COALESCE example with two numbers and NULL inputs (arguments) 
SELECT COALESCE(NULL,2,3) as COALESCE_Result

The output is as follows:

Since the first NOT Null value is 2, the COALESCE function has returned it, ignoring the value after it.

Let us now reverse the order of numbers in the arguments’ list with the following script:

-- Simple COALESCE example with two numbers reversed and NULL inputs (arguments) 
SELECT COALESCE(NULL,3,2) as COALESCE_Result 

The result set is below:

‘Number, String, and a NULL’ Example

Here we mix up the argument types by inputting a number, string, and a NULL value. Type the following code:

-- Simple COALESCE example with one number one string and NULL inputs (arguments) 
SELECT COALESCE(NULL,1,'Home') as COALESCE_Result

The results are as follows:

Although COALESCE function shows the correct results, picking up the first NOT NULL value, which is 1, it is not a good example. We should supply the same type of arguments. It is either numbers or characters or any other type of expression.

If we supply the string ‘Home’ as the first argument and the number 1 as the second argument, it will cause an error. That is why the expressions should be of the same type.

Let us try this by interchanging the two NOT NULL arguments:

-- Simple COALESCE example with one number one string first and NULL inputs (arguments) 
SELECT COALESCE(NULL,'Home',1) as COALESCE_Result

The output is below:

‘Two Strings with a NULL’ Example

If we input a NULL value followed by two string values, it will return the first string because it is NOT NULL. It will leave the second one out. Run the following script to see it in action:

-- Simple COALESCE example with two strings and NULL inputs (arguments) 
SELECT COALESCE(NULL,'Home','Office') as COALESCE_Result

The result set is below:

The above example proves that accurate results require supplying the same type of arguments to the COALESCE function. An argument itself can be a number, string, variable, or a column of the table.

Real-Case examples

Now we take a simple real-case scenario of the COALESCE function handling NULL values effectively.

An IT services provider company stores its customers’ details, falling into the following two categories:

1. Business Customers.
2. Individual Customers.

A business customer is a customer representing a company. They place orders on behalf of the company (business). An individual customer is a person ordering services personally and per personal use.

The business customers’ contacts are in the Customer table under the Work_Email column. The personal customers’ email addresses are in the Personal_Email column of the table.

Therefore, it populates either Work_Email or Personal_Email column for each customer, depending on the type.

Create the Customer Table

We’ll create a table called ‘Customer’ in the sample database CoalesceDB. Type the following script:

-- Using the sample database
Use CoalesceDB

-- Create Customer table
CREATE TABLE Customer
(CustomerId INT IDENTITY(1,1),
Work_Email VARCHAR(250) NULL,
Personal_Email VARCHAR(250) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerId]) 
)

Populate the Customer Table

Let us populate the customer table using the following script:

-- Using the sample database
Use CoalesceDB

-- Populate Customer table
INSERT INTO Customer
(Work_Email,Personal_Email)
values
('[email protected]',NULL),
(NULL,'[email protected]'),
(NULL,'[email protected]'),
('[email protected]',NULL),
('[email protected]',NULL),
(NULL,'[email protected]')

-- Important: Please note the above email addresses are for demo purposes only

View the Customer Table

Run the following script:

-- View customer table
SELECT [CustomerId], [Work_Email], [Personal_Email] FROM dbo.Customer

The output is as follows:

Viewing All Available Contacts using COALESCE

Now, if we would like to see all the available contacts of all the customers, ignoring NULLs, we can achieve this by using the COALESCE function as follows:

--Viewing all available contacts using COALESCE 
SELECT C.CustomerId,COALESCE(Work_Email,Personal_Email) AS Email
FROM dbo.Customer C

The results are:

Things to do

Congratulations! You have the basics of the COALESCE function and understand its use in handling the NULL values effectively.

Now, you might want to train the new skills further:

1. Try to create and populate two more columns, Work_Phone and Personal_Phone, in the Customer table. See if you could handle NULL values in these columns using the real-case example.
2. Try to create an SQL view to show you all the available email contacts of the customers.
3. Try passing all four columns (Work_Email, Personal_Email, Work_Phone, and Personal_Phone) into the function. Of course, you need to ensure they all have the same data type. See the results yourself.

Read also

A Practical Use of the SQL COALESCE Function

Top Answers to 5 Burning Questions on SQL COALESCE Function