使用包含 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 值的几个例子。