Specify a conditional value.

 

Syntax

 

<case expression> ::=

 

<case abbreviation> ::=

 

|

 

NULLIF ( <value expression> , <value expression> )

COALESCE ( <value expression> { , <value expression> }... )

 

<case specification> ::=

 

|

 

CASE <case operand> <simple when clause>... [ <else clause> ] END

CASE <searched when clause>... [ <else clause> ] END

 

<simple when clause> ::= WHEN <when operand> THEN <result>

 

<searched when clause> ::= WHEN <search condition> THEN <result>

 

<else clause> ::= ELSE <result>

 

<case operand> ::= <row value predicand>

 

<when operand> ::= <row value predicand>

 

<result> ::=

 

|

 

<value expression>

NULL

 

 

Usage

 

The CASE expression is similar in concept to the CASE statement found in some programming languages. It is used in SQL to provide a conditional value, and can be specified anywhere a value expression is allowed.

 

 

Notes

 

The searched CASE expression is the main syntactical version, and the most flexible one, since a search condition is specified for each individual WHEN clause.



 

The simple CASE expression is a handy shorthand that eliminates the need for repeating the same value in each WHEN clause when comparing the same value against different conditions.



 

The NULLIF abbreviation compares two values and returns null if the values are equal, else returning the first value specified. This is shorthand syntax for:



 


CASE WHEN value1 = value2 THEN NULL ELSE value1 END



 

The COALESCE abbreviation returns the first value in a list of comparable values that is not null, or null if all values in the list are the null value. This is shorthand syntax for:



 


CASE




WHEN value1 IS NOT NULL THEN value1


WHEN value2 IS NOT NULL THEN value2


WHEN valuen IS NOT NULL THEN valuen


ELSE NULL




END



 

If the optional ELSE clause is omitted, then ELSE NULL is implicit.



 

The resulting data type of a CASE expression is determined according to the rules for Result data types of aggregations.



 

Tip: Use COALESCE in arithmetic expressions to avoid calculations on a null value.

 

 

Examples

 

1)

The following example uses a searched CASE expression to determine a conditional value for the gender_description column:



 


SELECT




studentID,


studentName,


CASE




WHEN gender = 'F' THEN 'Female'


WHEN gender = 'M' THEN 'Male'


ELSE 'Not available'




END AS gender_description




FROM students



 

2)

The following example uses a simple CASE expression to determine a conditional value for the gender_description column:



 


SELECT




studentID,


studentName,


CASE gender




WHEN 'F' THEN 'Female'


WHEN 'M' THEN 'Male'


ELSE 'Not available'




END AS gender_description




FROM students



 

3)

The following example uses the NULLIF function to display a null in the rows where the orderTotal column has a value of 0:



 


SELECT




orderID,


NULLIF( orderTotal, 0 ) AS orderTotal




FROM orders


ORDER BY orderID



 

4)

The following example uses the COALESCE function to avoid calculations on potential null values:



 


SELECT




orderID,


COALESCE( orderTotal, 0 ) - COALESCE( amountPaid, 0 ) AS "Amount Due"




FROM orders


ORDER BY orderID



 

 


 

Conformance

 

SQL:2003 standard

-

Core SQL