本文共 1816 字,大约阅读时间需要 6 分钟。
最近在調優一個not in 子查詢語句時,當改為 left join 后,效率大幅提高。找資料重新學習下:
1. 很多朋友都說到not in子查詢中如果 有null 值導致查詢結果不正確的問題。
例子: 建两个表:test1 和 test2create table test1 (id1 int)
create table test2 (id2 int)insert into test1 (id1) values (1),(2),(3)
insert into test2 (id2) values (1),(2),(null) --另外提一點,字段盡可能不要允許null值,可以通過設置默認值。–因為null值比較的原因,以下查詢語句返回結果竟然為空,我們當然是希望得到 id1= 3 的記錄.
select id1 from test1 where id1 not in (select id2 from test2) 或 select ‘id1 = 3’ where 3 not in (1,2,null)原因:在SQL Server中,Null值并不是一个值,而是表示特定含义,其所表示的含义是“Unknow”,可以理解为未定义或者未知,因此任何与Null值进行比对的二元操作符结果一定为Null,包括Null值本身。而在SQL Server中,Null值的含义转换为Bool类型的结果为False。
例如:解決方法:
1、用 NOT EXISTS 代替select * FROM test1
where NOT EXISTS (select * from test2 where id2 = id1 )2、用LEFT JOIN 代替
select id1 from test1
LEFT JOIN test2 ON id2 = id1 where id2 IS NULL – 這個是實際使用時要注意:子查詢的表字段最好使用distint 防止返回結果重復記錄。例如 :select t1.id1 from test1 t1
LEFT JOIN (select distinct id2 test2) t2 ON t1.id1 = t2.id2 where t2.id2 IS NULL2. (not) in 子查詢查詢效率低下。
1)前面說過(Not) In对Null值处理,查詢時要确认查詢的字段列中是否有Null值(如果查詢字段設置為允許null值時)
2)查询字段如有索引也会失效. (not) in 中的子查询出来的数据会保存到临时表中,再执行主查询,利用临时表中的数据去核对主查询的where条件是否成立,而临时表是没有索引的。一个Not in 與 not exists對比的例子:
DBCC FREEPROCCACHE;
GO DBCC DROPCLEANBUFFERS; GO SET STATISTICS IO ON; SET STATISTICS TIME ON;select *from employee2 where empName not in (select empname from employee1)
select *From employee2 where not exists (select empname from employee1 where employee1.empName = employee2.empName )
通過上面的對比可以看出: 1)在查詢條件字段 允許為空的情況下,not in 的查詢效率要比 not exists 慢很多。 2)但在查詢字段不允許為空的情況下,兩者查詢效率基本相當。另外說下 IN和Exists:
IN 是非相关子查询,子查询先执行,且只执行一次,执行完毕后将值传递给外层查询; EXIST是相关子查询,将外层查询的一个元组传递给子查询,然后执行子查询,外层查询根据返回的结果集得到满足条件的记录,重复这个过程直到外层查询的所有元组都处理完毕。从这个过程来看,正常情况下非相关子查询应该比相关子查询效率高。
当然,如果(NOT)In 里面是已知固定的一些值,个人认为是可以用的.例如
SELECT * FROM User WHERE Dept IN (‘IT’,‘SALES’,‘OFFICE’,‘SER’)转载地址:http://zsmdi.baihongyu.com/