Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.


Free Beginners SQL Course!




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.


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!

Related Posts:

What is a Database NULL Value?

7 responses to “SQL COALESCE Function and NULL”
  1. […] this case you can use the SQL COALESCE function to assist.  This function will return the first non-NULL value from a list of parameters.  For […]

  2. […] SQL COALESCE Function and NULL […]

  3. Nice posting…

  4. semih

    excellent post
    keep posting please
    i was trying to understand what COALESCE means
    finally i understood from your tutorial :)

  5. K

    Very nice explanation! Thanks!

  6. V

    excellent post!

    1. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SQL Server