SingleStoreDB v8.1

CASE

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.

CREATE TABLE allviews (State varchar(10), Product varchar(5), Views int);
INSERT INTO allviews VALUES
("CA","E",50), 
("NY","C",15), 
("NY","F",40), 
("CA","A",60), 
("CA","D",10), 
("NY","B",20), 
(NULL,"G",40);

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      |
+-------+------------+
|    20 | New York   |
|    50 | California |
|    10 | California |
|    60 | California |
|    15 | New York   |
|    40 | N/A        |
|    40 | New York   |
+-------+------------+

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      |
+----------+

Implicit Collation

When character_set_server is set to utf8, string literals with characters using 4-byte encoding are implicitly assigned binary collation and processed as a sequence of bytes rather than characters. This implicit conversion to binary collation causes string functions to return unexpected results. To avoid using implicit binary collation, either use explicit type casting or use database columns defined with the utf8mb4 character set.

For more information, refer to Implicit Collation in Special Cases.

In this section: