如何在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);

使用UNION

我们的第一反应大概就是看看能不能使用UNION，我们先得到表1-表2的内容，然后再得到表2-表1的内容，再把两者union起来，如下面的语法所示：

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

这样之后，就会得到下面这样的结果：

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

但是这样有一个问题，就是每一个表格我们都访问两次。有没有更好的方法呢？

使用NATURAL FULL JOIN

我们可以使用下面的语句来实现：

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;

这时候的返回值是这样的：

为什么呢，因为NATURAL FULL JOIN其实是使用相同的列值就行join的，这里我们两个表各产生了一个新的列t1和t2，在相同值的行，t1和t2列的值就会都存在，而在不同值的行，则只会有一列存在，可能是t1列或者有可能是t2列。我们只要把这种情况过滤出来就可以了。

这里需要注意的是，当值是NULL的时候，判断的时候会遵循下面这个表格：

这里我们可以看到R IS NULL和NOT R IS NOT NULL其实是不同的，所以我们上面提到的natural full join其实等同于下面这样：

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;

另外一种写法是我们使用JOIN … USING来替代NATRUAL 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;

这个没什么好说的， 就是把所有的列都用using来join，其实和NATRUAL是一样的，不过这个更灵活一点。

或者你也可以使用JOIN … ON来替代上面的实现，本质还是一样的：

-- 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;

优缺点分析

使用FULL JOIN和UNION相比较有什么优缺点呢：

优点：

• 每个表格都只被访问了一次
• 这里的比较是基于列的名字的，不是列的index，设置可以自定义部分列来进行比较。

缺点：

• 假如需要基于索引的列比较（列的名字可能不同），你就需要把相应的列改成通用的名字。
• 假如有很多重复的列，速度可能会比较慢
• UNION和EXCEPT认为NULL值是“没有区别”，而NATURAL JOIN则不是这样的，我们需要通过下面这种方法来解决

当有NULL的数据的时候处理

有NULL的时候，我们需要使用DISTINCT来处理：

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;

这样我们就简单介绍了如何来比较两个表格，希望对你有所帮助。

参考文章：https://blog.jooq.org/2020/08/05/use-natural-full-join-to-compare-two-tables-in-sql/