CASE Expressions

A CASE expression is used like an if-then-else construct to conditionally return a value. There are two forms of syntax for CASE expression, the simple form and the searched form. The simple form is used to test a single operand for equality against multiple expressions. The searched form of a CASE expression is more flexible and allows for testing multiple conditions.

For more information on using CASE expressions in the select list of SELECT statement, see The Select List.

Simple CASE Expression

The simple CASE expression implies equality (=) is used for comparisons. One <common_operand> is tested against multiple values. It is frequently used to transform one set of values to another longer form.

Syntax: 
CASE <common_operand>
    WHEN <expression> THEN <result>
   [WHEN <expression> THEN <result>
    . . .]
        [ELSE <result>]
END
For example: 
SELECT lastname,
    CASE gender
        WHEN ‘M’ THEN ‘Male’
        WHEN ‘m’ THEN ‘Male’
        WHEN ‘0’ THEN ‘Male’
        WHEN ‘F’ THEN ‘Female’
        WHEN ‘f’ THEN ‘Female’
        WHEN ‘1’ THEN ‘Female’
        ELSE ‘Unknown’
    END
FROM employees
Equality expressions cannot be used to test for NULL so you cannot use a simple CASE expression to test for NULL. You must use a searched CASE expression with IS NULL or IS NOT NULL when testing for NULL.

Searched CASE Expression

The searched CASE expression is good to use when you want to work with a greater range of tests. Any boolean expression qualifies as a WHEN <expression>.

Syntax: 
CASE
    WHEN <condition> THEN <result>
   [WHEN <condition> THEN <result>
    . . .]
        [ELSE <result>]
END
The result of the first true boolean expression is returned. For example, the following SELECT statement uses a searched CASE expression to prevent division by zero: 
SELECT ProductID, Name, ProductNumber, Cost, ListPrice,
CASE
    WHEN ListPrice = 0 THEN NULL
    ELSE Cost / ListPrice
END AS CostOfGoodSold
FROM Product
The searched CASE expression can also be used to test a result column for NULL whereas a simple CASE expression cannot. The following simple CASE expression is invalid: 
SELECT
CASE middle_name
    WHEN NULL THEN ‘<NULL>’
    ELSE
        middle_name
END
In SQL you cannot use equality to test if a column is NULL (empty). Instead you must use 'IS NULL' as follows: 
SELECT
CASE
    WHEN middle_name IS NULL THEN ‘<NULL>’
    ELSE
        middle_name
END

CASE Comparisons

When you use a simple CASE expression or a searched CASE expression, the following behavior of comparisons is true:
  • WHEN clauses are evaluated in the order they are defined.
  • When an ELSE clause is provided and none of the previous WHEN clause evaluations match, the ELSE result is returned.
  • If an ELSE clause is omitted and none of the previous WHEN clause evaluations match, NULL (SQL NULL) is returned.
Related concepts