如何在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的时候,判断的时候会遵循下面这个表格:

Expression 
R IS NULL 
true 
false 
true 
false 
false 
R IS NOT 
false 
true 
false 
false 
true 
NULL 
NULL 
degree 
degree 
degree 
degree 
degree 
null 
not null 
> 1: all null 
> I: some null 
> I: none null 
false 
true 
false 
true 
true 
NOT R IS W 
true 
false 
true 
true 
false

这里我们可以看到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/

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *