Switch statement construct. Evaluates the specified expression/condition and returns the result from the matching expression.

Syntax

(CASE
  WHEN condition THEN val1
  WHEN condition THEN val2
  ...
  ELSE defaultval END)

Switching off a single expression:

(CASE expression
  WHEN case1 THEN val1
  WHEN case2 THEN val2
  ...
  ELSE defaultval END)

Arguments

• Any SQL objects

Return Type

The return type depends on the type of arguments val1, val2, etc. For example, if val1 or val2 is a string, the return type will be a string, depending on which condition is met. If they are both integers, the return type will be an integer, and so on.

Remarks

• The expressions inside CASE statements are evaluated sequentially, and the evaluation stops with the first matching condition. If there are multiple matching conditions, the value of the first matching WHEN clause is returned.
• CASE statements have two formats:
  • The first format uses a collection of independent WHEN conditions, and it returns the result from the first WHEN condition that returns TRUE.
  • The second format uses only one expression or condition, and it returns the results from the first WHEN clause which satisfies the condition.
• In case none of the WHEN clauses evaluate to the CASE expression or return TRUE, the CASE statement returns the expression specified in the ELSE clause.

Examples

Example 1

The following example shows how to use a CASE statement using only one condition.

SELECT * FROM allviews;
****
+-------+---------+-------+
| State | Product | Views |
+-------+---------+-------+
| CA    | E       |    50 |
| NY    | C       |    15 |
| NY    | F       |    40 |
| CA    | A       |    60 |
| CA    | D       |    10 |
| NY    | B       |    20 |
| NULL  | G       |    40 |
+-------+---------+-------+

SELECT Views,
(CASE State
  WHEN 'CA' THEN 'California'
  WHEN 'NY' THEN 'New York'
  ELSE 'N/A'
  END) As 'State'
FROM allviews;
****
+-------+------------+
| Views | State      |
+-------+------------+
|    50 | California |
|    15 | New York   |
|    40 | New York   |
|    60 | California |
|    10 | California |
|    20 | New York   |
|    40 | N/A        |
+-------+------------+

Example 2

The following example shows how to use CASE statements with independent WHEN conditions.

SELECT
( CASE
  WHEN 1=0 THEN 3
  WHEN 0=1 THEN 5
  ELSE 8
  END) AS 'Choices';
****
+---------+
| Choices |
+---------+
|       8 |
+---------+

Example 3

The following example shows that the value of only the first matching WHEN clause is returned.

SELECT
( CASE 'ohai'
  WHEN 'yo' THEN 'sup'
  WHEN 'hello' THEN 'hi'
  WHEN 'ohai' THEN 'bai'
  WHEN 'ohai' THEN 'ok'
  ELSE 'huh?'
  END) As 'Greeting';
****
+----------+
| Greeting |
+----------+
| bai      |
+----------+

Was this Article Helpful?

Thanks For Your Feedback!