CASE WHEN

Grammar Description

The CASE WHEN statement is used to calculate a list of conditions and return one of multiple possible result expressions. CASE WHEN can compare conditions equal to and range. When the first condition is encountered, it will return, and no further comparison will be made. If there is no condition, it will return the result in else. If there is no else, it will return NULL.

CASE has two formats, both of which support the optional ELSE parameter. :

A simple CASE function compares an expression with a set of simple expressions to determine the result.
CASE Search function calculates a set of Boolean expressions to determine the result.

Grammar Structure

-Grammar Structure 1:

CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...] [ELSE result] END

The CASE syntax here returns the result of the first branch with value=compare_value of true.

-Syntax Structure 2:

CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END

The CASE syntax here returns the result of the first branch with condition of true.

If there is no value=compare_value or condition is true, then the result corresponding to ELSE is returned, and if there is no ELSE branch, then the result is returned.

note

The CASE statement cannot have an ELSE NULLclause, and theCASEstatement must end withEND`.

Example

mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+-----------------------------------------------------+
| case when 1 > 0 then true else false end |
+-----------------------------------------------------+
| true |
+-----------------------------------------------------+
1 row in set (0.00 sec)

CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.01 sec)

INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
Query OK, 6 rows affected (0.01 sec)

mysql> SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 GROUP BY b;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| case when avg(a) >= 0 then Positive else Negative end |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Positive |
| Positive |
| Positive |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)