We all know that a Null value is a field with no value. The statements that we are running daily will have to deal with Null values, especially when it comes to strings concatenation (adding strings together).
Let’s start with the SQL Coalesce definition. The Coalesce SQL function returns first nonnull expression among its arguments. The rule of thumb here is that Null plus anything equals zero. It’s like zero times anything equals zero. For example, the big problem here is when working with things like addresses. To be more specific, it’s a problem when we have to combine e.g. a street name with a postal code, additional address line, etc. So, using the logic from above, if one of those has a Null value, the whole thing is going to be Null.
Now, if you are not so familiar with the SQL Coalesce expression, it’s probably best to get familiar with the Case expression first and its formats because the Coalesce SQL expression is a syntactic shortcut for the Case expression. We will explain this using a simple example later on but feel free to check out the following article for more details on the Case expression: Querying data using the SQL Case statement
If we want to prove the Coalesce SQL definition from above, consider the following example that uses the “AdventureWorks2012” database:
|
1 2 3 4 5 6 |
SELECT p.Title, p.FirstName, p.MiddleName, p.FirstName+' '+p.LastName AS FirstLastName, COALESCE(p.FirstName+' '+p.MiddleName+' '+p.LastName, p.FirstName+' '+p.LastName, p.FirstName, p.LastName) AS FullName FROM Person.Person AS p; |
Before we run this query, let’s take a look at the columns from Object Explorer. Note that both “FirstName” and “LastName” are not null which means that they cannot be empty:
For the sake of the example simplicity, execute the script below to nullify the “LastName” column:
|
1 2 |
ALTER TABLE Person.Person ALTER COLUMN LastName NVARCHAR(50) NULL; GO |
Right-click the “Person” table from Object Explorer and choose the Edit Top 200 Rows command. Change the “LastName” value from “Sánchez” to Null of the very first record:
We just made sure that this record has the first name but not the last name. So, right now if we are to do just first name plus last name part of our initial query the result will be Null:
|
1 2 3 4 5 |
SELECT p.Title, p.FirstName, p.MiddleName, p.FirstName+' '+p.LastName AS FirstLastName FROM Person.Person AS p; |
Here is the result:
As we mentioned at the beginning, this is a problem. But, if we run the query with the SQL Server Coalesce expression, by the Coalesce SQL definition it will return the first non-Null value:
As it can be seen from the query, we can do an expression one, comma, expression two, etc. We are basically saying add them all together using the SQL Coalesce expressions:
- First name, middle name, last name – if any of those is Null, go to the next one
- First name, last name – same rules apply as above as well for below
- First name – this is where we hit the Coalesce and return the first non-Null value (Ken)
- Last name – the last one will not get hit
We can even go an extra mile and say if all of the above is Null (first name, middle name, last name), provide a default value saying “No name”:
|
1 2 3 4 5 6 |
SELECT p.Title, p.FirstName, p.MiddleName, p.FirstName+' '+p.LastName AS FirstLastName, COALESCE(p.FirstName+' '+p.MiddleName+' '+p.LastName, p.FirstName+' '+p.LastName, p.FirstName, p.LastName, 'No name') AS FullName FROM Person.Person AS p; |
And get something like this if the first, middle, and last name are Null:
To wrap things up, hopefully you’ve seen how handy SQL Coalesce can be when you have to combine many fields and when some of them are Null. You can play with the output and basically see how it would look like if they are all there, what it would look like if a couple of them are there, and you can go all the way down the list to make all the possible combinations including the case when all of the fields have Null value and ensure that the result/output will not be a Null using the SQL Coalesce function.
Furthermore, note that the SQL Server Coalesce expression and IsNull function have a similar purpose but behave differently. SQL Coalesce can be used on anything that can return a Null value and not just characters as shown in this article, but number too, etc. The IsNull function has only two parameters, where SQL Coalesce has X parameters and it can go for as long as you can keep adding expressions.
I hope this has been informative for you and you have learned something about the Coalesce SQL function. Thanks for reading.
He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.
Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.
See more about Bojan at LinkedIn
View all posts by Bojan Petrovic
- Visual Studio Code for MySQL and MariaDB development - August 13, 2020
- SQL UPDATE syntax explained - July 10, 2020
- CREATE VIEW SQL: Working with indexed views in SQL Server - March 24, 2020