集合运算符和 NULL

集合运算符 UNION、EXCEPT 和 INTERSECT 对 NULL 的处理方式与搜索条件中对 NULL 的处理方式不同。此差异是使用集合运算符的主要原因之一。

比较行时，集合运算符将 NULL 值视为彼此相等。而相反，在搜索条件中将 NULL 与 NULL 进行比较时，结果是未知的（不为真）。

这种差异的一个结果是，query-1 EXCEPT ALL query-2 结果集中的行数始终是各个查询结果集中的行数之差。

以两个表 T1 和 T2 为例，其中每个表都具有以下列：

col1 INT,
col2 CHAR(1)

表和数据的设置如下：

CREATE TABLE T1 (col1 INT, col2 CHAR(1));
CREATE TABLE T2 (col1 INT, col2 CHAR(1));
INSERT INTO T1 (col1, col2) VALUES(1, 'a');
INSERT INTO T1 (col1, col2) VALUES(2, 'b');
INSERT INTO T1 (col1) VALUES(3);
INSERT INTO T1 (col1) VALUES(3);
INSERT INTO T1 (col1) VALUES(4);
INSERT INTO T1 (col1) VALUES(4);
INSERT INTO T2 (col1, col2) VALUES(1, 'a');
INSERT INTO T2 (col1, col2) VALUES(2, 'x');
INSERT INTO T2 (col1) VALUES(3);

表中的数据如下：

表 T1。

col1	col2
1	a
2	b
3	(NULL)
3	(NULL)
4	(NULL)
4	(NULL)

表 T2。

col1	col2
1	a
2	x
3	(NULL)

一个询问同时出现在 T1 和 T2 中的行的查询如下：

SELECT T1.col1, T1.col2
   FROM T1 JOIN T2
   ON T1.col1 = T2.col1
   AND T1.col2 = T2.col2;

T1.col1	T1.col2
1	a

(3, NULL) 这一行不出现在结果集中，因为 NULL 和 NULL 之间的比较不为真。相比之下，使用 INTERSECT 运算符处理该问题时，则会包括含有 NULL 的行：

SELECT col1, col2
   FROM T1
   INTERSECT
      SELECT col1, col2
         FROM T2;

col1	col2
1	a
3	(NULL)

以下查询使用搜索条件列出 T1 中那些不出现在 T2 中的行：

SELECT col1, col2
   FROM T1
   WHERE col1 NOT IN (
       SELECT col1
         FROM T2
         WHERE T1.col2 = T2.col2 )
   OR col2 NOT IN (
       SELECT col2
         FROM T2
         WHERE T1.col1 = T2.col1 );

col1	col2
2	b
3	(NULL)
4	(NULL)
3	(NULL)
4	(NULL)

通过比较，并未将 T1 中包含 NULL 的行排除在外。相比之下，使用 EXCEPT ALL 处理该问题会排除在这两个表中都出现的包含 NULL 的行。在此情况下，即认为 T2 中的 (3, NULL) 行与 T1 中的 (3, NULL) 行相同。

SELECT col1, col2
   FROM T1
   EXCEPT ALL
      SELECT col1, col2
         FROM T2;

col1	col2
2	b
3	(NULL)
4	(NULL)
4	(NULL)

尽管如此，EXCEPT 运算符还是有更多限制。它会将 T1 中的两个 (3, NULL) 行都去除，并将其中一个 (4, NULL) 行作为重复行排除在外。

SELECT col1, col2
   FROM T1
   EXCEPT
      SELECT col1, col2
         FROM T2;

col1	col2
2	b
4	(NULL)

使用DocCommentXchange讨论此页。