CASE (SQL)

Synopsis

CASE WHEN search_condition THEN value_expression
  [ WHEN search_condition THEN value_expression ... ]
  [ ELSE value_expression ] 
  END  

CASE value_expression WHEN value_expression THEN value_expression
  [ WHEN value_expression THEN value_expression ... ]
  [ ELSE value_expression ]
  END

Description

The CASE expression allows you to make comparison tests on series of values, returning when it encounters the first match.

The CASE expression comes in two forms: Simple and Searched.

The Simple CASE expression tests a series of value expressions (specified by a WHEN clause) to see if they are equal to a given value expression:

SELECT
CASE Field1
  WHEN 1 THEN 'ONE'
  WHEN 2 THEN 'TWO'
  ELSE NULL
END
FROM MyTable

The value associated with the first matching expression is returned as the value of the CASE expression.

Numeric value_expression values may have different data types. The data type returned is the type most compatible with all of the possible result values, the data type with the highest data type precedence. For numeric value_expression values CASE returns the largest length, precision, and scale from all of the possible result values. A result value of NULL has the lowest data type precedence; however, if all result values are NULL, the data type returned is VARCHAR.

The Searched CASE expression tests a series of search conditions (specified by a WHEN clause), finds the first WHEN condition that evaluates to true, and returns the value associated with it:

SELECT
CASE
  WHEN Field1 = 1 THEN 'ONE'
  WHEN Field1 = 2 THEN 'TWO'
  ELSE NULL
END
FROM MyTable

With either form of CASE expression, you can use an ELSE clause to specify what value to return if none of the WHEN clause conditions are true. If you omit the ELSE clause and none of the WHEN clause conditions are true, CASE returns NULL.

A CASE comparison that tests for NULL must use the IS NULL or IS NOT NULL keyword phrase. NULL is not a data value (it represents the absence of a value). For this reason, any equality or arithmetic test for NULL always returns false. A CASE expression that compares NULL and any data value always returns false. For example, NULL < 1 and NULL > 1 both return false. A CASE expression that equates NULL with NULL also returns false.

The end of a CASE expression is marked by an END token.

Examples

The following query is an example of a Simple CASE expression, where specified field values are replaced by supplied values. Note the use of the RetireAge column alias after the END keyword; the optional AS keyword is omitted in this example:

SELECT Name,
CASE Age
  WHEN 65 THEN 'Retire this year'
  WHEN 64 THEN 'Retire next year' 
  ELSE 'Past retirement age '|| Age
END RetireAge
FROM Sample.Person
WHERE Age > 63
ORDER BY Age

The following query is another example of a Simple CASE expression. This query labels rows with certain Home_State values as either “Northern NE” or “Southern NE”, and sets all other Home_State values in this column to NULL. It uses the As clause to label this column as “NewEnglanders”, and also displays Names and the original Home_State values. The resulting rows are ordered first by the NewEnglanders column (in descending order), and within this alphabetically by Home_State, and then by Name.

SELECT Name,
CASE Home_State
  WHEN 'VT' THEN 'Northern NE'
  WHEN 'NH' THEN 'Northern NE'
  WHEN 'ME' THEN 'Northern NE'
  WHEN 'MA' THEN 'Southern NE'
  WHEN 'CT' THEN 'Southern NE'
  WHEN 'RI' THEN 'Southern NE'
  ELSE NULL
END AS NewEnglanders, Home_State
FROM Sample.Person
ORDER BY NewEnglanders DESC,Home_State,Name

The following query is an example of a Searched CASE expression. It uses logical operators (greater than (>), logical AND (&), logical OR (!)) to specify a boolean statement for each WHEN clause. The first WHEN clause that tests True sets the value expression that follows the THEN keyword. In this example, the Age and Home_State field values are used to identify three types of Yankees: Old Yankees, Yankees (residents of the six New England states), and likely fans of the New York Yankees baseball team:

SELECT Name,
CASE
WHEN Age > 55 & Home_State = 'VT' 
   ! Home_State='ME' ! Home_State='NH'
   ! Home_State='MA' ! Home_State='CT'
   ! Home_State='RI'
THEN 'Old Yankee'
WHEN Home_State = 'VT' 
   ! Home_State='ME' ! Home_State='NH'
   ! Home_State='MA' ! Home_State='CT'
   ! Home_State='RI'
THEN 'Yankee'
WHEN Home_State='NY' THEN 'Yankees Fan'
   ELSE Home_State
END AS Yankees
FROM Sample.Person

The following example shows that any comparison with NULL always returns false:

SELECT TOP 5 Name,
CASE NULL
  WHEN NULL THEN 'Null = Null'
  WHEN 0 THEN 'Null = 0'
  WHEN '' THEN 'Null = empty string'
  WHEN CHAR(0) THEN 'Null = CHAR(0)'
  ELSE 'Null Arithmetic Invalid'
END
FROM Sample.Person

The following example shows how to use CASE with a field that has NULLs:

SELECT TOP 20 Name,
CASE
  WHEN FavoriteColors IS NULL THEN 'No Colors'
  ELSE $LISTTOSTRING(FavoriteColors,':')
END
FROM Sample.Person

CASE is not limited to use in queries, as shown in the following example:

INSERT INTO SQLUser.MyStudents (Name, PxTs) VALUES (
CASE ? 
    WHEN 'a' THEN 'Alice' 
    WHEN 'b' THEN 'Barney' 
    ELSE 'Unknown' END, 
CURRENT_TIMESTAMP)

See Also

• SQL functions: DECODE, GREATEST, LEAST, NULLIF, COALESCE

• ObjectScript function: $CASE