sql 取两个表_如何在SQL中得到两个表的不同行

点上面“东哥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;

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