点上面“东哥IT笔记”,关注并星标
每天一篇业界最新技术分享
有时候,我们需要比较两个表的差异,希望能够返回两个表不同的行,那怎么才能有效快速地得到这个结果,本文就来做一个简单的介绍:
表格准备
我们来假设有下面两个表(PostgreSQL 语法):
CREATETABLE t1 (a INT, b INT, c INT);CREATETABLE t2 (a INT, b INT, c INT);INSERTINTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);INSERTINTO 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.* FROMt1) t1 NATURAL FULL JOIN ( SELECT 't2' AS t2, t2.* FROMt2) t2WHERE NOT (t1, t2) IS NOT NULL;
这时候的返回值是这样的:
a |b |c |t1|t2|
--|--|--|--|--|
1| 2| 3|t1| |
10|11|12| |t2|
为什么呢,因为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.* FROMt1) t1 NATURAL FULL JOIN ( SELECT 't2' AS t2, t2.* FROMt2) t2WHERE t1 IS NULLOR t2 IS NULL;
另外一种写法是我们使用JOIN … USING来替代NATRUAL JOIN,如下:
-- Use JOIN .. USING, instead of NATURAL JOINSELECT *FROM ( SELECT 't1' AS t1, t1.* FROMt1) t1 FULL JOIN ( SELECT 't2' AS t2, t2.* FROMt2) t2 USING (a, b, c)WHERE NOT (t1, t2) IS NOT NULL;
这个没什么好说的,就是把所有的列都用using来join,其实和NATRUAL是一样的,不过这个更灵活一点。
或者你也可以使用JOIN … ON来替代上面的实现,本质还是一样的:
-- Use JOIN .. ON, instead of JOIN .. USINGSELECT coalesce(t1.a, t2.a) AS a, coalesce(t1.b, t2.b) AS b, coalesce(t1.c, t2.c) AS c, t1.t1, t2.t2FROM ( SELECT 't1' AS t1, t1.* FROMt1) t1 FULL JOIN ( SELECT 't2' AS t2, t2.* FROMt2) 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.t2FROM ( SELECT 't1' AS t1, t1.* FROMt1) t1 FULL JOIN ( SELECT 't2' AS t2, t2.* FROMt2) 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;
这样我们就简单介绍了如何来比较两个表格,希望对你有所帮助。