SQL Pocket Guide, 2nd Edition

CASE Expressions

CASE expressions let you implement if-then-else functionality in your SQL statements.

Simple CASE Expressions

Simple CASE expressions let you correlate a list of values to a list of alternatives. For example:

SELECT u.name,
   CASE u.open_to_public
      WHEN 'y' THEN 'Welcome!'
      WHEN 'n' THEN 'Go Away!'
      ELSE 'Bad code!'
   END AS column_alias
FROM upfall u;

Simple CASE expressions are useful when you can directly link an input value to a WHEN clause by means of an equality condition. If no WHEN clause is a match, and no ELSE is specified, the expression returns null.

Searched CASE Expressions

Searched CASE expressions let you associate a list of alternative return values with a list of true/false conditions. For example:

SELECT u.name,
   CASE
      WHEN u.open_to_public = 'y' THEN 'Welcome!'
      WHEN u.open_to_public = 'n' THEN 'Go Away!'
      ELSE 'Bad code!'
   END AS column_alias
FROM upfall u;

Null is returned in the event that no condition is TRUE and no ELSE is specified. If multiple conditions are TRUE, only the first such condition is executed.