在SQL中使用自然全额联接来比较两个表的详细指南

在SQL中，有几种方法可以比较两个类似的表。假设PostgreSQL的语法，我们可能有这样的模式：

CREATE TABLE t1 (a INT, b INT, c INT);
CREATE TABLE t2 (a INT, b INT, c INT);
INSERT INTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
INSERT INTO t2 VALUES            (4, 5, 6), (7, 8, 9), (10, 11, 12);

现在可以按照Chris Saxon的建议，使用UNION 和EXCEPT ：

你可以用以下方法比较两个表

t1减去t2联合t2减去t1

有一个更好的方法，你对每个表查询一次......但你需要列出所有的列

幸运的是，@StewAshton已经建立了一个整洁的包，为你生成#SQL:)https://t.co/7OroPV6JdY

- Chris Saxon (@ChrisRSaxon)2020年8月5日

在PostgreSQL中，我们可以这样写：

(TABLE t1 EXCEPT TABLE t2) 
UNION
(TABLE t2 EXCEPT TABLE t1)
ORDER BY a, b, c

注意，TABLE x 只是标准的SQL，而PostgreSQL则是SELECT * FROM x 的语法糖。我们会得到：

a |b |c |
--|--|--|
 1| 2| 3|
10|11|12|

不幸的是，这需要对每个表进行两次访问。我们可以用一个访问来做吗？

使用自然全连接

是的！使用。使用NATURAL FULL JOIN ，这是这个深奥的运算符的另一个罕见的用例。 假设没有NULL值，我们可以这样写：

SELECT *
FROM (
  SELECT 't1' AS t1, t1.* FROM t1
) t1 NATURAL FULL JOIN (
  SELECT 't2' AS t2, t2.* FROM t2
) t2 
WHERE NOT (t1, t2) IS NOT NULL;

这将产生：

a |b |c |t1|t2|
--|--|--|--|--|
 1| 2| 3|t1|  |
10|11|12|  |t2|

为什么？因为NATURAL JOIN 是使用两个表的所有共享列名进行连接的语法糖，而FULL JOIN 确保我们也能检索到没有被连接谓词匹配的列。另一种写法是：

-- Use JOIN .. USING, instead of NATURAL JOIN
SELECT *
FROM (
  SELECT 't1' AS t1, t1.* FROM t1
) t1 FULL JOIN (
  SELECT 't2' AS t2, t2.* FROM t2
) t2 USING (a, b, c)
WHERE NOT (t1, t2) IS NOT NULL;

或者：

-- Use JOIN .. ON, instead of JOIN .. USING
SELECT 
  coalesce(t1.a, t2.a) AS a,
  coalesce(t1.b, t2.b) AS b,
  coalesce(t1.c, t2.c) AS c,
  t1.t1,
  t2.t2
FROM (
  SELECT 't1' AS t1, t1.* FROM t1
) t1 FULL JOIN (
  SELECT 't2' AS t2, t2.* FROM t2
) t2 ON (t1.a, t1.b, t1.c) = (t2.a, t2.b, t2.c) 
WHERE NOT (t1, t2) IS NOT NULL;

不幸的是，从PostgreSQL 12开始，这产生了一个错误：

ERROR: FULL JOIN只支持可合并连接或可哈希连接的连接条件

优点和缺点

与使用UNION 和EXCEPT 的集合运算符解决方案相比，优点和缺点：优点

• 每个表只被访问一次
• 现在的比较是基于名称的，而不是基于列索引的，也就是说，如果只有部分列是共享的，它仍然可以工作。

缺点

• 如果需要基于索引的列比较（因为表在结构上是相同的，但不共享完全相同的列名），那么我们就必须将每个单独的列重命名为一个共同的列名。
• 如果有重复的数据，就会有一个笛卡尔的乘积，这可能会使这个解决方案变得相当缓慢
• UNION 和 ，将 的值视为 "非独立"。 则不是这种情况。见下面的变通方法EXCEPT NULL NATURAL JOIN

当数据中存在NULL 值时

在有NULL 值的情况下，我们不能再使用NATURAL JOIN 或JOIN .. USING 。我们可以使用 [DISTINCT predicate](http://blog.jooq.org/2012/09/21/the-is-distinct-from-predicate/):

SELECT 
  coalesce(t1.a, t2.a) AS a,
  coalesce(t1.b, t2.b) AS b,
  coalesce(t1.c, t2.c) AS c,
  t1.t1,
  t2.t2
FROM (
  SELECT 't1' AS t1, t1.* FROM t1
) t1 FULL JOIN (
  SELECT 't2' AS t2, t2.* FROM t2
) t2 ON (t1.a, t1.b, t1.c) IS NOT DISTINCT FROM (t2.a, t2.b, t2.c) 
WHERE NOT (t1, t2) IS NOT NULL;

行值表达式NULL谓词

观察行值表达的深奥的NULL 谓词的用法，它使用下面的真值表：

+-----------------------+-----------+---------------+---------------+-------------------+
| Expression            | R IS NULL | R IS NOT NULL | NOT R IS NULL | NOT R IS NOT NULL |
+-----------------------+-----------+---------------+---------------+-------------------+
| degree 1: null        | true      | false         | false         |  true             |
| degree 1: not null    | false     | true          | true          |  false            |
| degree > 1: all null  | true      | false         | false         |  true             |
| degree > 1: some null | false     | false         | true          |  true             |
| degree > 1: none null | false     | true          | true          |  false            |
+-----------------------+-----------+---------------+---------------+-------------------+

是的，R IS NULL 和NOT R IS NOT NULL 在SQL中不是一回事...这只是另一种写法：

SELECT *
FROM (
  SELECT 't1' AS t1, t1.* FROM t1
) t1 NATURAL FULL JOIN (
  SELECT 't2' AS t2, t2.* FROM t2
) t2 
WHERE t1 IS NULL
OR t2 IS NULL;