# **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**:

```text
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**:

```text
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 <font size=3>`CASE` statement cannot have an ELSE NULL` clause, and the `CASE` statement must end with `END`.
:::
</font>

## **Example**

```text
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)
```

```text
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)
```