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 matchingWHEN
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 firstWHEN
condition that returnsTRUE
.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 theCASE
expression or returnTRUE
, theCASE
statement returns the expression specified in theELSE
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.