SQL Coalesce Function and NULL

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 seem what ever 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.

What is COALESCE?

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 an account table whose accountFlag may be NULL. To display “Missing” rather than NULL, we can use COALESCE. Here is an example:

SELECT accountNO, COALESCE(accountFlag, 'Missing')
FROM account

In this example COALESCE(accountFlag, ‘Missing’) returns accountFlag if it isn’t NULL, but if it is, then it goes to the next value in the list, which is ‘Missing.’

Comparing SQL COALESCE to CASE

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,
CASE
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'
END
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!

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA.He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame.Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

Click Here to Leave a Comment Below 0 comments