Case Expressions
Wed, 03/04/2009 - 20:02 | 
hdanzl
Specify a conditional value.
Syntax
|
| |
NULLIF ( <value expression> , <value expression> ) COALESCE ( <value expression> { , <value expression> }... ) |
|
| |
CASE <case operand> <simple when clause>... [ <else clause> ] END CASE <searched when clause>... [ <else clause> ] END |
|
| |
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 |
Tags:
Questions? Problems?
- Interested to buy but something put you off?
- Can't find what you're looking for?
- Have a suggestion?
Let us know. If you're having trouble contacting us, please check here.
Site Updates
- Dec 4 2024 - 1:53pm
- Dec 4 2024 - 1:48pm
- Nov 19 2024 - 1:04pm
- Oct 17 2024 - 10:27am
- Jul 24 2024 - 1:22pm