SQL CASE Expression

You can use CASE expressions in the select list of SELECT statements. A CASE expression is used like an if-then-else construct to conditionally return a value. Using a CASE expression in the select list of a SELECT statement can help categorize results based on their values or help apply different calculations to a result based on its value.

The following query can be used to find out which employees are 'in-state' versus 'out-of-state': 
SELECT empid, CASE state WHEN 'IL' THEN 'in-state' ELSE 'out-of-state' END AS location FROM employee

There are two forms of syntax for CASE expressions: the simple form and searched form.

Syntax of a simple CASE expression: 
CASE <common_operand>
    WHEN <expression> THEN <result>
   [WHEN <expression> THEN <result>
    . . .]
        [ELSE <result>]
END
Syntax of a searched CASE expression 
CASE
    WHEN <condition> THEN <result>
   [WHEN <condition> THEN <result>
    . . .]
        [ELSE <result>]
END

CASE Comparisons

When a simple case expression is used, the <common_operand> is compared for equality to each <expression>. If a match is found, the <result> of the matching WHEN <expression> is returned.

When a searched CASE expression is used, each WHEN <condition> must evaluate to true or false. WHEN clauses are evaluated in the order they are defined. The result corresponding to the first true test is returned.

When an ELSE <result> is provided and none of the previous WHEN evaluations match, the ELSE<result> is returned. If an ELSE <result> is omitted and none of the previous WHEN evaluations match, NULL (SQL NULL) is returned.

All <result> values must be of the same data type. Evaluating the result data types is not done until the query is executed. Therefore, it is possible that a bad SELECT statement can be created which later causes an exception to be returned from the node when it processes the bad SELECT statement.

Restrictions on CASE Expressions

When using a CASE expression in the select list of a SELECT statement, the restrictions on using expressions in select lists from the section Select List Expressions apply to CASE expressions. In addition, the following additional restrictions apply:
  • CASE expressions cannot contain functions which use the DISTINCT keyword
  • All resulting values of the CASE expression must be of the same data type. Evaluating the result data types is not done until the query is executed. Therefore, it is possible that a bad SELECT statement can be created which later causes an exception to be returned when the query is executed.
  • CASE expressions composed of subexpressions which parse but do not make sense does not return an error.
Restrictions on Nested CASE Expressions
When CASE expressions contain other CASE expressions, an internal limit that is different from the expression depth limit might take effect. This internal limit is used to prevent the exhaustion of resources when parsing CASE expressions. The point at which this limit is reached during parsing can vary based upon the composition of the query. Reaching the limit shows the following error: 
Error Code  = Resource limit reached
Description = SQL parser stack overflow

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. 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>. 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 have to use 'IS NULL' as follows: 
SELECT
CASE
    WHEN middle_name IS NULL THEN ‘<NULL>’
    ELSE
        middle_name
END