SQL: Visual QuickStart Guide

Checking for Nulls with COALESCE()

The COALESCE() function returns the first non-null expression among its arguments. COALESCE() often is used to display a specific value instead of a null in a result, which is helpful if your users find nulls confusing. COALESCE() is just shorthand for a common form of the searched CASE expression:

COALESCE(expr1, expr2, expr3)

is equivalent to:

CASE
  WHEN expr1 IS NOT NULL THEN expr1
  WHEN expr2 IS NOT NULL THEN expr2
  ELSE expr3
END

To return the first non-null value:

• Type:

  COALESCE(expr1, expr2,...)
  

  expr1, expr2,..., represent one or more comma-separated expressions. All expressions must be of the same type or must be implicitly convertible to the same type. Each expression is evaluated in order until ...