Knowing when to use the SQL
COALESCE function is a lifesaver when you’re dealing with
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
COALESCE works well here:
SELECT COALESCE(parkalias, parkname) as ParkAlias, city, state FROM Parks
SELECT COALESCE(parkalias, parkname) as ParkAlias,
In this example,
COALESCE(parkalias, parkname) returns the value found in
parkalias if the value is not
NULL; however, if it is
parkname is returned.
What SQL would you write if you wanted to display ‘
Unknown’ if the
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,
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
We want to list the
answerID and first non
From what we have learned, we can use
COALESCE(option1, option2, option3, 'No Option Selected')
This is a shortcut for this
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'
CASE is used in more circumstances, but when replacing
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.