Click here to Skip to main content
15,883,705 members
Articles / Database Development / SQL Server


Rate me:
Please Sign up or sign in to vote.
4.50/5 (4 votes)
23 Feb 2020MIT2 min read 10.8K   3   2
SQL COALESCE function and how you can test for null, and when present, substitute it for another value
Whenever you deal with NULL touching an expression and want to avoid rendering the result as NULL, a solution is required to break out of the cycle. With the SQL COALESCE function, you can test for NULL, and when present, substitute NULL for another value. This post takes a look at COALESCE and also compares SQL COALESCE to CASE.

Knowing when to use the SQL COALESCE function is a lifesaver when you’re dealing with NULL.

As you know, NULL is a tricky concept, and it seems whatever NULL “touches” in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is, how can you display another value instead?

This is where SQL COALESCE comes into play. With this function, you can test for NULL, and when present, substitute NULL for another value.


COALESCE is a built-in SQLServer Function. Use COALESCE when you need to replace a NULL with another value. It takes the form:

COALESCE(value1, value2, ..., valuen)

It returns the first non NULL from the value list.

Consider the baseball parks from Lahmans Baseball Database. If we want to display the parkname in place of the parkalias, which may be null, COALESCE works well here:

SELECT COALESCE(parkalias, parkname) as ParkAlias, city, state FROM Parks

-- Answer
SELECT COALESCE(parkalias, parkname) as ParkAlias,
FROM   Parks

In this example, COALESCE(parkalias, parkname) returns the value found in parkalias if the value is not NULL; however, if it is NULL, then parkname is returned.

What SQL would you write if you wanted to display ‘Unknown’ if the parkalias is NULL? Why not try to write the query… here is a start…

SELECT parkalias, parkname, city, state FROM Parks

-- Answer
SELECT COALESCE(parkalias, 'Unknown') as parkalias,
FROM  Parks


Did you know SQL COALESCE is a shortcut for the CASE statement?

Consider the table survey, which has the columns answerID, option1, option2, option3.

We want to list the answerID and first non NULL option.

From what we have learned, we can use COALESCE as:

SELECT answerID,
       COALESCE(option1, option2, option3, 'No Option Selected')
FROM   survey

This is a shortcut for this CASE statement:

SELECT answerID,
          WHEN option1 is not NULL then option1
          WHEN option2 is not NULL then option2
          WHEN option3 is not NULL then option3 
          ELSE 'No Option Selected'
FROM   survey

CASE is used in more circumstances, but when replacing NULL, COALESCE is a handy shortcut!

For the record, SQL Server has many more interesting functions to learn and explore. If you haven’t done so already, check out these string functions to manipulate text data!


This article, along with any associated source code and files, is licensed under The MIT License

Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here

Comments and Discussions

BugYou need to do some research Pin
MadMyche25-Feb-20 8:34
professionalMadMyche25-Feb-20 8:34 
GeneralRe: You need to do some research Pin
essentialSQL6-Mar-22 5:54
essentialSQL6-Mar-22 5:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.