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
Arguments
Argument | Description |
---|---|
search_condition | An SQL boolean expression. |
value_expression | An SQL expression (such as a literal value or field name.) |
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. If the returned value is a string, the collation of the returned value is the collation of the first value_expression that has a collation type that may not be coerced into another collation type. For example, if the first value_expression is a string that uses %SQLUPPER collation, the returned value will use %SQLUPPER collation, regardless of the collation of the value_expression that is returned.
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)