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 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.
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 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
SELECT COALESCE(parkalias, parkname) as ParkAlias,
city,
state
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
SELECT COALESCE(parkalias, 'Unknown') as parkalias,
parkname,
city,
state
FROM Parks
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!
The post SQL COALESCE Function and NULL appeared first on Essential SQL.