Bojan Petrovic

Querying data using the SQL Coalesce function

September 18, 2018 by

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:

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:


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:

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:

  1. First name, middle name, last name – if any of those is Null, go to the next one
  2. First name, last name – same rules apply as above as well for below
  3. First name – this is where we hit the Coalesce and return the first non-Null value (Ken)
  4. 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”:

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.


See more

Seamlessly integrate a powerful, free SQL formatter into SSMS and/or Visual Studio with ApexSQL Refactor. ApexSQL Refactor is a SQL query formatter but it can also obfuscate SQL, refactor objects, safely rename objects and more – with nearly 200 customizable options.





Bojan Petrovic

Bojan Petrovic

Bojan has a passion for working with computers, servers, and network technology, and electronics. He likes to stay updated with developments and the most recent technology. He currently works at ApexSQL LLC as a Support Engineer with a bachelor’s degree in IT Network and Electronics Technology.

He helps customers with any technical issues and does quality assurance (testing applications and preparing for production) for ApexSQL Complete, ApexSQL Refactor, and ApexSQL Search free add-ins.

View all posts by Bojan Petrovic
Bojan Petrovic

Latest posts by Bojan Petrovic (see all)

Functions, String functions, T-SQL

About Bojan Petrovic

Bojan has a passion for working with computers, servers, and network technology, and electronics. He likes to stay updated with developments and the most recent technology. He currently works at ApexSQL LLC as a Support Engineer with a bachelor’s degree in IT Network and Electronics Technology. He helps customers with any technical issues and does quality assurance (testing applications and preparing for production) for ApexSQL Complete, ApexSQL Refactor, and ApexSQL Search free add-ins. View all posts by Bojan Petrovic

220 Views