查詢在一張表不在另外一張表的記錄SQL Server Join方式

文章目錄

• 一般解法（效率低）
• 效率分析
• 使用串連解決
• 資料準備

參考文獻

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執行上述語句，得到如所示結果：

效率分析

但是仔細分析我們可以發現，如果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

上述查詢語句的查詢結果如下：

因為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

查詢結果如所示：

但是我們又發現上述查詢結果有2列，也就是a表和b表的串連查詢結果，但是我們只需要a表中的內容，所以對上述查詢稍作修改：

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條記錄到taSET NOCOUNT ON;GODECLARE @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);

時隔三個月再來看這道題目，又有新的發現，之前還是只是半知半解，在寫完SQL Server Join方式這篇部落格以後基本就明白這道題目的核心了，核心是：我應該使用何種聯結方式來查詢結果。

我們能夠寫出來的最直觀的TSQL語句應該是：

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

然後我們看看這個語句的查詢計劃：

從我們可以發現使用了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

上述查詢的執行執行計畫如所示：

從我們可以發現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

其執行計畫如所示：

從可以看出：

1. 因為查詢列上都沒有索引，所以查詢出來的結果不一定是排序的，這樣sql server幫我們做了排序操作。
2. 在做完排序操作以後進行的是Merge Join操作，整個查詢所使用的TotalSubtreeCost=0.69，好於Nested Loop，比Hash Match效能差。

所以我們在回答上面題目的時候，必須說明使用Hash Match，而不只是給出left join的答案，之所以查詢結果最有是因為sql server幫我們分析了使用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 Loopsselect ta.id from ta left loop join tb on ta.id=tb.id where tb.id is NULL--Nested Loopsselect ta.id from ta left merge join tb on ta.id=tb.id where tb.id is NULL--Merge Joinselect ta.id from ta left hash join tb on ta.id=tb.id where tb.id is NULL--Hash Match