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.
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.
According to Microsoft documentation, this function is compatible with many Microsoft SQL technologies, including the following:
- All supported versions of SQL Server.
- 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.
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:
- Business Customers.
- 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 ('firstname.lastname@example.org',NULL), (NULL,'email@example.com'), (NULL,'firstname.lastname@example.org'), ('email@example.com',NULL), ('firstname.lastname@example.org',NULL), (NULL,'email@example.com') -- 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:
- 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.
- Try to create an SQL view to show you all the available email contacts of the customers.
- 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.
- SQL Server Business Intelligence (BI) – Basic Understanding - April 13, 2021
- Learn to Multicast Data Using SSIS Packages - March 23, 2021
- How to Set up and Test-Run the Environment to Multicast Data with SSIS - March 12, 2021