In which conditions, MySQL CASE statement return NULL?

MySQLMySQLi Database

As we know that if no comparison or condition is true then CASE statement returns the result specified after ELSE statement. But what if there is no ELSE statement, then in this situation, CASE statement would return NULL. Following is 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 query below, using the data from table ‘Students’, returns NULL because no student belongs to country WI.

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)
raja
Published on 21-Feb-2018 06:13:34
Advertisements