SQL null:首选区分未知和无值

Question

假设我有一个简单的表，例如：

CREATE TABLE authors (
    id INT PRIMARY KEY,
    name VARCHAR(40),
    born DATE,
    died DATE
);

born和died日期都是可选的，因此它们可以包含NULL。

大概是尚未出生的作者，不会把它放进桌子，因此解释NULL的明显方法是日期是未知的。

但是，died日期存在一些歧义。零可能暗示日期未知，或者作者尚未死亡。

对未知日期和尚未发生的事件的首选方式是什么？

对于它的价值，我通常使用PostgreSQL，MySQL/Mariadb和SQL Server，因此此问题不是特定的DBMS。

Answer 1

处理此操作的一种方法是添加一个新列deceased，该列指示给定的作者是否已过期。这使您的died日期字段释放了作者是否死亡的负责。在此设计下，如果作者尚未死亡(deceased是错误的(，那么我们就不在乎死亡日期。如果作者死了，那么NULL值只意味着该日期确实是未知的。

Answer 2

可能的解决方案是删除died列并添加下表：

CREATE TABLE deaths (
    id INT PRIMARY KEY REFERENCES authors(id),
    died DATE
);

在这种情况下，没有记录暗示作者没有死亡，而died中的NULL意味着作者已经死了，但是日期未知。

然后可以提取数据数据如下：

SELECT
    a.id, a.name, a.born,
    CASE WHEN d.id IS NULL THEN 'living' ELSE coalesce(d.died,'unknown') END
FROM authors a LEFT JOIN deaths d ON a.id=d.id;

d.id列用于确定是否存在匹配行，否则作者被视为生存。如果有匹配行，则结果是died日期或合并的替代品。

Answer 3

这对蒂姆的答案进行了详细说明。我认为最好的方法是：

CREATE TABLE authors (
    id INT PRIMARY KEY,
    name VARCHAR(40),
    isDead int not null,  -- well, could be a bit or boolean or tinyint
    bornDate DATE,
    diedDate DATE,
    constraint chk_authors_isDead check (isDead in (0, 1)),  -- valid values
    constraint chk_authors_isDead_diedDate check (isDead = 1 or diedDate is NULL),
    constraint check_authors_bornDate_diedDate check (bornDate <= diedDate)  -- you might want to require that they are actually old enough to have written somthing
);

此代码验证以下条件：

• 新的isDead列仅处理0和1的值。
• 当DiedDate不是NULL时，isDead是1。
• 作者在死前出生。

检查约束是标准的SQL，并由大多数数据库支持 - 但不是由MySQL。