【转】查询在一张表不在另外一张表的记录

我会带着你远行 2021-09-27 14:38 341阅读 0赞

## 参考文献 ##

[http://hi.baidu.com/zdfgng/blog/item/dd5f88359a1cd0260b55a9ce.html][http_hi.baidu.com_zdfgng_blog_item_dd5f88359a1cd0260b55a9ce.html]

## 题目 ##

假如要查询在a表中存在，但是在b表中不存在的记录，应该如何查询。为了便于说明，我们假设a表和b表都只有一个字段id，a表中的记录为\{1,2,3,4,5\}，b表中的记录为\{2,4\}，那么我们需要通过一个sql查询得到\{1,3,5\}这样的结果集。

### 一般解法（效率低） ###

看到这个题目，我们首先想到的可能就是**not in**这样的关键字，具体的查询语句如下：

select ta.* from ta where ta.id not in(select tb.id from tb)

上述查询语句的查询结果集确实是\{1,3,5\}，用navicat执行上述语句，得到如下图所示结果：

![2012060112505872.jpg][]

### 效率分析 ###

但是仔细分析我们可以发现，如果b表很长，那么执行上述的查询语句，需要用a表中的字段去匹配b表中的每一个字段，相当于是a表的每一个字段都要遍历一次b表，效率非常低下。**（只要a中的字段不在b表中那么肯定要遍历完b表，如果a表中的字段在b表中，那么只要遍历到就退出，进行a表中下一个字段的匹配）**

### 使用连接解决 ###

连接查询使我们平时进行sql查询用到最多的操作之一了，相对于上述**not in**关键字，我们使用连接查询的效率更高。因为我们需要搜索的是a表中的内容，所以使用a表左连接b表，这样b表中会补null，查询语句如下：

select * from ta left join tb on ta.id=tb.id

上述查询语句的查询结果如下：

![2012060113315985.jpg][]

因为a、b两表中字段id相同，所以上述b表中的id字段变成了id1。仔细观察由可以发现，我们需要的结果集\{1,3,5\}所对应的id1字段都是null。这样我们在上述的查询语句中加入条件即可完成对只在a表中，但不在b表中的结果集的插叙，查询语句如下：

select * from ta left join tb on ta.id=tb.id where tb.id is null

查询结果如下图所示：

![2012060113413449.jpg][]

但是我们又发现上述查询结果有2列，也就是a表和b表的连接查询结果，但是我们只需要a表中的内容，所以对上述查询稍作修改：

select ta.* from ta left join tb on ta.id=tb.id where tb.id is null

查询结果如下图所示：

![2012060113425163.jpg][]

以上就是我们所要求的查询结果。

## 详解(PS：2012-9-7) ##

### 数据准备 ###

![ContractedBlock.gif][]

时隔三个月再来看这道题目，又有新的发现，之前还是只是半知半解，在写完[SQL Server Join方式][SQL Server Join]这篇博客以后基本就明白这道题目的核心了，核心是：我应该使用何种联接方式来查询结果。

我们能够写出来的最直观的TSQL语句应该是：

select ta.* from ta where ta.id not in(select tb.id from tb)

然后我们看看这个语句的查询计划：

![2012090715214823.png][]![2012090715215656.png][]

从上图我们可以发现使用了Nested Loops的联接方式，但是我们知道nested loop联接方式的使用场景是：**比较适合于两个比较小的结果集做联接，或者至少是Outer table的结果集比较小。**而上面的outer table是ta，它是大表，所以可以发现nested loop不适合。注意：虽然上面的查询语句中没有join字段，但是还是使用了join。

假如我们使用left join 来写查询语句的话，sql server会帮我们选择何种联接方式呢？测试如下：

select ta.id from ta left join tb on ta.id=tb.id where tb.id is NULL--Hash Match

上述查询的执行执行计划如下图所示：

![2012090715293642.png][]![2012090715294375.png][]

从上图我们可以发现sql server帮我们选择了使用**Hash Match**。这是因为在上述联接中，ta是大表，ta和tb两表之间数据量差距很大，还有ta和tb都没有索引。从执行计划的TotalSubtreeCost中也可以看出来，使用Hash Match的TotalSubtreeCost=0.12，而是用Nested Loop的TotalSubtreeCost=1.03。可以发现Hash Match性能比Nest Loop好很多。

那么使用Merge Join能，起性能如何？我们可以通过使用sql hint来建议sql server使用特定的联接方式，执行如下TSQL语句：

select ta.id from ta left merge join tb on ta.id=tb.id where tb.id is NULL--Merge Join

其执行计划如下图所示：

![2012090715341382.png][]![2012090715342053.png][]

从上图可以看出：

1.  因为查询列上都没有索引，所以查询出来的结果不一定是排序的，这样sql server帮我们做了排序操作。
2.  在做完排序操作以后进行的是Merge Join操作，整个查询所使用的TotalSubtreeCost=0.69，好于Nested Loop，比Hash Match性能差。

所以我们在回答上面题目的时候，必须说明使用Hash Match，而不只是给出left join的答案，之所以查询结果最有是因为sql server帮我们分析了使用Hash Match性能最优。

所有查询方式：

select ta.* from ta where ta.id not in(select tb.id from tb)
    select ta.id from ta where ta.id not in(select tb.id from tb)--Nested Loops
    select ta.id from ta left loop join tb on ta.id=tb.id where tb.id is NULL--Nested Loops
    select ta.id from ta left merge join tb on ta.id=tb.id where tb.id is NULL--Merge Join
    select ta.id from ta left hash join tb on ta.id=tb.id where tb.id is NULL--Hash Match

作者：[xwdreamer][]

出处：[http://www.cnblogs.com/xwdreamer][xwdreamer]

欢迎任何形式的转载，但请务必注明出处。

转自：[查询在一张表不在另外一张表的记录][Link 1]

[http_hi.baidu.com_zdfgng_blog_item_dd5f88359a1cd0260b55a9ce.html]: http://hi.baidu.com/zdfgng/blog/item/dd5f88359a1cd0260b55a9ce.html
[2012060112505872.jpg]: /images/20210923/01a239945b3d40db863adbaaac32fc73.png
[2012060113315985.jpg]: /images/20210923/fa05decdf61f4d02b5eddcae7fb1934a.png
[2012060113413449.jpg]: /images/20210923/7d67f8c169904ebf87a45823671473d4.png
[2012060113425163.jpg]: /images/20210923/dc98dc8f15d04cecaabd850664ce06c2.png
[ContractedBlock.gif]: /images/20210923/c270819b38c64f83a469494e9bdaa833.png
[SQL Server Join]: http://www.cnblogs.com/xwdreamer/archive/2012/09/07/2674756.html
[2012090715214823.png]: https://pic002.cnblogs.com/images/2012/80896/2012090715214823.png
[2012090715215656.png]: /images/20210923/46c50a8329e64af598a1a4e8a5b408fa.png
[2012090715293642.png]: https://pic002.cnblogs.com/images/2012/80896/2012090715293642.png
[2012090715294375.png]: /images/20210923/a3e75c55482d452c8cda7669bd01e83e.png
[2012090715341382.png]: https://pic002.cnblogs.com/images/2012/80896/2012090715341382.png
[2012090715342053.png]: /images/20210923/bee169209cae41a4b73195e77ccd40cd.png
[xwdreamer]: http://www.cnblogs.com/xwdreamer
[Link 1]: https://www.cnblogs.com/xwdreamer/archive/2012/06/01/2530597.html