Under what circumstances does the MySQL CASE statement return NULL?

We know that if there is no comparison or the condition is true, the CASE statement will return the result specified after the ELSE statement. However, if there is no ELSE statement, then the CASE statement will return NULL in this case. Here's an example to demonstrate it.

Example

mysql> Select CASE 100
    -> WHEN 150 THEN 'It is matched'
    -> WHEN 200 THEN 'It is not matched'
    -> END As 'It Returns NULL';
+-----------------+
| It Returns NULL |
+-----------------+
| NULL            |
+-----------------+
1 row in set (0.00 sec)

The following query uses data from the table "Students" and returns NULL because no students belong to the WI country.

mysql> Select SUM(CASE WHEN country = 'USA' THEN 1 ELSE 0 END) AS USA,
    -> SUM(CASE WHEN country = 'UK' THEN 1 ELSE 0 END) AS UK,
    -> SUM(CASE WHEN country = 'INDIA' THEN 1 ELSE 0 END) AS INDIA,
    -> SUM(CASE WHEN country = 'Russia' THEN 1 ELSE 0 END) AS Russia,
    -> SUM(CASE WHEN country = 'France' THEN 1 ELSE 0 END) AS France,
    -> SUM(CASE WHEN country = 'NZ' THEN 1 ELSE 0 END) AS NZ,
    -> SUM(CASE WHEN country = 'Australia' THEN 1 ELSE 0 END) AS Australia,
    -> SUM(CASE WHEN country = 'WI' THEN 1 END) AS WI
    -> From Students;
+------+------+-------+--------+--------+------+-----------+------+
| USA  | UK   | INDIA | Russia | France | NZ   | Australia | WI   |
+------+------+-------+--------+--------+------+-----------+------+
| 2    | 1    | 2     | 1      | 1      | 1    | 1         | NULL |
+------+------+-------+--------+--------+------+-----------+------+
1 row in set (0.00 sec)

The above is the detailed content of Under what circumstances does the MySQL CASE statement return NULL?. For more information, please follow other related articles on the PHP Chinese website!