Even though people sometimes think of Structured Query Language (SQL) as a database system, that's not strictly correct.
Instead, SQL is used to manage information within databases, which makes it a relational database management system (RDBMS).
Given that, it's primary purpose is to extract information from databases based on user-submitted queries using predefined functions.
Anyone who has worked with SQL is likely familiar with its basic commands such as SELECT, AND OR, WHERE, and HAVING. When it comes to the concept of defining NULL conditions, however, most programmers will be more familiar with the ISNULL function as opposed to COALESCE.
Although it's underutilized, COALESCE is a powerful, advanced logic function with capabilities extending beyond related NULL commands. When and how should you use COALESCE in SQL? We'll go over the basics below to get you started.
The COALESCE Function
Using COALESCE in either SQL Server or MySQL allows you to search an object – that is, a table – in a relational database until you find the first non-NULL value. It's often seen as being much like the ISNULL function, so we'll talk about the distinctions between the two a little later below.
To use COALESCE, you would write:
COALESCE ("expression 1", "expression 2", ...)
To see this in action, let's say we have the following contact information in a table called Email_Info:
Now, let's perform a search to find the preferred email address to use for contacting these people as per the following three rules:
- 1If there is a business email address, use it.
- 2If there is not a business email address, use the school email address.
- 3If there is not a business or school email address but there is a personal email address, use it.
Then, we can use COALESCE to perform a search with these parameters in the Email_Info table:
SELECT Name, COALESCE (Business_email, School_email, Personal_email) Contact_Email FROM Email_Info;
Finally, we'd have these results as the query output:
COALESCE & CASE
As you may have noticed by now, COALESCE is very similar to the CASE function, which uses if-then-else logic to evaluate data and subsequently return a value when the first in a series of conditions is met. There's a good reason for that: COALESCE is actually a shortcut for the CASE function. Let's take another look at the COALESCE function syntax:
COALESCE ("expression 1", "expression 2", ...)
Using CASE to perform the same search would look like this:
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
So, if you've been using CASE to perform these types of database queries, COALESCE will allow you to do the same action with less code.
The ISNULL Function
To understand how COALESCE is distinct from the ISNULL function it's commonly compared to, we need to take a quick look at how ISNULL operates. That depends, however, on if you're using SQL Server or MySQL, both of which are RDMBSs.
SQL Server ISNULL
In SQL Server, ISNULL is used to replace a NULL value with another value. For example, I might have the following table called Class_Data:
To calculate the total number of students as well as replace the NULL value with a number of my choice, I'd write the following in SQL:
SELECT SUM (ISNULL(Students,25)) FROM Class_Data;
Then, I'd receive the following result by adding 27 + 30 + 25 (the number replacing the NULL value):
In MySQL, however, ISNULL is used to determine if an expression is NULL or not. For example, I could write the following two queries:
With the first expression, 4 x 4 is 16 – not a NULL value – so the result would be:
ISNULL(4*4) returns 0
With the second expression, 4 x 0 is 0 – a NULL value – so the result would be:
ISNULL(4*0) returns 1
COALESCE vs. ISNULL
While both COALESCE and ISNULL return values based on NULL vs. non-NULL query results, there are some important distinctions between the two:
- 1COALESCE is an American National Standards Institute (ANSI) standard, but ISNULL is not.
- 2ISNULL will evaluate data only once, while COALESCE will do this multiple times.
- 3ISNULL can only use two parameters, but COALESCE can use a variable number.
- 4The first parameter's data type will be used by ISNULL. COALESCE follows CASE rules which return the highest precedence data type value.
- 5Validations are performed differently. An ISNULL which is NULL is converted to an integer (int). A data type must be provided when using COALESCE.
- 6Result expression NULLability is also different. An ISNULL return value is always considered NOT NULLable. Non-NULL parameters when using COALESCE will be considered NULL.
Additional COALESCE Resources
Now that we've gone over the basics of using COALESCE, how it relates to CASE, and how it's different from ISNULL, you probably want to learn even more about how to use it. The websites below offer detailed articles and tutorials, extensive examples of code, and handy tips for its use.
SQL Server and MySQL are Microsoft products, so a good place to start is with the COALESCE documentation at the company's website. In addition to a detailed overview of COALESCE, there are four examples of code – two simple and two complex – which illustrate the function's use:
- 1Simple: Using the AdventureWorks2012 database, a search is performed to return the first non-NULL value in a column.
- 2Complex: After creating a table with three columns for hourly wage, salary, and commission, a search is performed to find total salaries paid.
- 3Simple: After performing a search of a table with clothing product information, there's a discussion explaining why the result set includes unexpected information due to a lack of defined parameters.
- 4Complex: The salary table from example 2 above is queried again, this time for only non-NULL values in its columns.
MSSQLTips.com has two useful articles: The Many Uses of COALESCE in SQL Server and Deciding between COALESCE and ISNULL in SQL Server. Each one begins with an explicit problem statement and the rest of the article provides a detailed discussion and solution.
The first one provides a basic overview of COALESCE. After that, there's a discussion with examples demonstrating how this function can be used to pivot data from the AdventureWorks2012 database.
Then, you'll learn about using COALESCE with multiple arguments as well as perform multiple SQL statements. You should also look at the extensive reader comments at the end of the article which cover pertinent topics and troubleshooting issues.
The second article begins with a brief discussion including examples of code showing how the COALESCE and ISNULL functions use data precedence differently. Then, additional extended examples of these functions in action demonstrate the key differences in performance. Again, you should check out the comments at the end which offer useful information.
Plus, think ISNULL always runs faster than COALESCE on SQL Server? There are some revealing results from four different, simple tests using two variables which were performed 500,000 times to compare run times:
- 1Both NULL arguments
- 2First argument NULL
- 3Second argument NULL
- 4Neither argument NULL
COALESCE was faster in the first two tests, the third one was a tie, and ISNULL was quicker in the final one.
This article begins with a description of the COALESCE function. After that, there are two detailed examples demonstrating different scenarios where it's useful: determining customer charges at an Internet service provider and dealing with a customer's unnamed service as a query result.
One excellent feature is the inclusion of screenshots of both the tables and code being used as opposed to just having that information on the webpage itself.
To learn some additional features of the COALESCE function, you should check out this tutorial which also uses screenshots to show input and output.
First, there's an example using string data. Then, the second example uses numerical data. Finally, there is a practical example which queries an employee contact information database – much like the example about sorting email addresses above – but with many more data fields included.
Not only do COALESCE and ISNULL handle data precedence differently, COALESCE's data precedence can also change when running on MySQL versus SQL Server even though it is an ANSI standard.
If you're generating error messages about converting varchar values to integers, this is well worth looking at.
Even experienced SQL Server and MySQL programmers may not be that familiar with the COALESCE function and the capabilities it offers.
The end goal of coding, however, is always the same: write the most powerful programs possible with the least amount of code.
To that end, you should use COALESCE whenever possible to perform the types of searches and queries detailed above.