O'Reilly logo

SQL: Visual QuickStart Guide by Chris Fehily

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required