# 查询在一张表不在另外一张表的记录

## 参考文献

http://hi.baidu.com/zdfgng/blog/item/dd5f88359a1cd0260b55a9ce.html

## 题目

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

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

### 使用连接解决

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

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

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

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

### 数据准备

View Code
use TESTDB3
--1.创建表,堆结构,ta,大表
CREATE TABLE ta
(
id   INT
);

--2.创建表,堆结构,tb,小表
CREATE TABLE tb
(
id   INT
);

--3.插入10000条记录到ta
SET NOCOUNT ON;
GO
DECLARE @i int;
SET @i = 1;
WHILE @i <= 10000 BEGIN
INSERT INTO ta
SELECT @i;
SET @i = @i + 1;
END;
GO

--4.往tb中插入少数数据
insert into tb values(1);
insert into tb values(111);
insert into tb values(11);
insert into tb values(11111111);
insert into tb values(1222222);

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

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

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

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

View Code
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

posted @ 2012-06-01 13:29  xwdreamer  阅读(24347)  评论(3编辑  收藏  举报