使用包含 NULL 值的列

在 MySQL 和其他 SQL 方言中，NULL 值具有特殊屬性。

請考慮下表，其中包含求職者，他們所在的公司以及他們離開公司的日期。NULL 表示申請人仍在公司工作：

CREATE TABLE example
(`applicant_id` INT, `company_name` VARCHAR(255), `end_date` DATE);

+--------------+-----------------+------------+
| applicant_id | company_name    | end_date   |
+--------------+-----------------+------------+
|            1 | Google          | NULL       |
|            1 | Initech         | 2013-01-31 |
|            2 | Woodworking.com | 2016-08-25 |
|            2 | NY Times        | 2013-11-10 |
|            3 | NFL.com         | 2014-04-13 |
+--------------+-----------------+------------+

你的任務是撰寫一個返回 2016-01-01 之後所有行的查詢，包括仍在公司工作的所有員工（具有 NULL 結束日期的員工）。這個選擇宣告：

SELECT * FROM example WHERE end_date > '2016-01-01';

未能包含任何具有 NULL 值的行：

+--------------+-----------------+------------+
| applicant_id | company_name    | end_date   |
+--------------+-----------------+------------+
|            2 | Woodworking.com | 2016-08-25 |
+--------------+-----------------+------------+

根據 MySQL 文件 ，使用算術運算子<，>，=和<>的比較本身返回 NULL 而不是布林值 TRUE 或 FALSE。因此，具有 NULL end_date 的行既不大於 2016-01-01 也不小於 2016-01-01。

這可以通過使用關鍵字 IS NULL 來解決：

SELECT * FROM example WHERE end_date > '2016-01-01' OR end_date IS NULL;

+--------------+-----------------+------------+
| applicant_id | company_name    | end_date   |
+--------------+-----------------+------------+
|            1 | Google          | NULL       |
|            2 | Woodworking.com | 2016-08-25 |
+--------------+-----------------+------------+

當任務涉及像 MAX() 和 GROUP BY 子句這樣的聚合函式時，使用 NULL 會變得更加複雜。如果你的任務是為每個 applicant_id 選擇最近使用的日期，則以下查詢似乎是合乎邏輯的第一次嘗試：

SELECT applicant_id, MAX(end_date) FROM example GROUP BY applicant_id;

+--------------+---------------+
| applicant_id | MAX(end_date) |
+--------------+---------------+
|            1 | 2013-01-31    |
|            2 | 2016-08-25    |
|            3 | 2014-04-13    |
+--------------+---------------+

但是，知道 NULL 表明申請人仍然在公司工作，結果的第一行是不準確的。使用 CASE WHEN 為 NULL 問題提供了一個解決方法：

 SELECT
    applicant_id,
    CASE WHEN MAX(end_date is null) = 1 THEN 'present' ELSE MAX(end_date) END
    max_date
FROM example
GROUP BY applicant_id;

+--------------+------------+
| applicant_id | max_date   |
+--------------+------------+
|            1 | present    |
|            2 | 2016-08-25 |
|            3 | 2014-04-13 |
+--------------+------------+

此結果可以連線回原始的 example 表，以確定申請人上次工作的公司：

SELECT 
  data.applicant_id,
  data.company_name,
  data.max_date
FROM (
  SELECT 
    *,
    CASE WHEN end_date is null THEN 'present' ELSE end_date END max_date
  FROM example
) data
INNER JOIN (
 SELECT
   applicant_id,
   CASE WHEN MAX(end_date is null) = 1 THEN 'present' ELSE MAX(end_date) END max_date
 FROM
   example
 GROUP BY applicant_id
) j
ON data.applicant_id = j.applicant_id AND data.max_date = j.max_date;

+--------------+-----------------+------------+
| applicant_id | company_name    | max_date   |
+--------------+-----------------+------------+
|            1 | Google          | present    |
|            2 | Woodworking.com | 2016-08-25 |
|            3 | NFL.com         | 2014-04-13 |
+--------------+-----------------+------------+

這些只是在 MySQL 中使用 NULL 值的幾個例子。