sql - 在PostgreSQL(可能还有其他引擎)中，为什么UNION语句将NULL值视为相同，而UNIQUE约束却不这样呢？

Question

我了解SQL标准在作为UNIQUE约束的一部分的列中允许多个NULL值。

我不明白的是为什么UNION构造(至少在PostgreSQL中)将NULL值视为相同。例如:

$ select * from tmp_a;
 a | b
---+---
 a | b
 a |
   |
(3 rows)

$ select * from tmp_b;
 a | b
---+---
 a | c
 a |
   |
(3 rows)

$ select a, b from tmp_a union select a, b from tmp_b order by 1, 2;
 a | b
---+---
 a | b
 a | c
 a |
   |
(4 rows)

Answer 1

SQL-92标准中的通用规则如下:

13.1'声明游标'(记住ORDER BY是游标的一部分)
一般规则3b:

the following special treatment of null values. Whether a sort key value that is null is considered greater or less than a non-null value is implementation-defined, but all sort key values that are null shall either be considered greater than all non-null values or be considered less than all non-null values.

SQL-89对IMO的声明也更加清晰:

Although x = y is unknown if both x and y are NULL values, in the context of GROUP BY, ORDER BY and DISTINCT, a NULL value is identical to or is a duplicate of another NULL value.

我猜想PostgreSQL正在按照 UNION的要求执行某种操作来删除重复项，并且按照标准将 NULL值组合在一起。