在哪些情況下，MySQL CASE 語句會返回 NULL？

眾所周知，如果沒有比較或條件為真，則 CASE 語句將返回ELSE語句之後的result 。但是如果沒有ELSE語句，則在這種情況下，CASE 語句將返回 NULL。下面是一個示例來說明這一點。

示例

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)

下面的查詢使用表‘Students’中的資料，因為沒有學生屬於國家 WI 而返回 NULL。

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)

阿爾瓊·塔庫爾

更新於： 11-Feb-2020

1K+ 瀏覽

開啟你的 職業生涯

透過完成課程獲得認證

開始