SQL中的In VS Exists 以及 not in VS not exists VS outer join 1 Exists的用法

SQL中的In VS Exists 以及 not in VS not exists VS outer join

本文转自【http://hi.baidu.com/imhager/item/563401c1df619e25a1b50a5d?qq-pf-to=pcqq.c2c】hager 博客

1 Exists的用法

1.1 在 Select语句中使用EXISTS
SELECT *FROM suppliers
WHERE EXISTS
(select *    from orders    where suppliers.supplier_id = orders.supplier_id)

1.2 在 Select语句中使用Not EXISTS
   SELECT * FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
1.3 在删除语句中使用
   DELETE FROM suppliers WHERE EXISTS
(select *     from orders    where suppliers.supplier_id = orders.supplier_id);
1.4 在Update语句中使用
UPDATE suppliers 
SET supplier_name = ( SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id) 
WHERE EXISTS   ( SELECT customers.name    FROM customers    WHERE customers.customer_id = suppliers.supplier_id); 
1.5 在Insrt语句中使用
INSERT INTO suppliers(supplier_id, supplier_name)
SELECT account_no, name FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

2 查询的相互转换
2.1 In,Exists和join的相互转换
以下SQL语句是相等的（假设supplier_id不为空)
SELECT * FROM suppliers
WHERE supplier_id in 
(select supplier_id from orders)

SELECT * FROM suppliers
WHERE EXISTS
(select *    from orders    where suppliers.supplier_id = orders.supplier_id)

SELECT * FROM suppliers
inner join orders on suppliers.supplier_id = orders.supplier_id

2.2 not In,not Exists和 outer join的相互转换
以下SQL语句是相等的（假设supplier_id不为空)

SELECT * FROM suppliers
WHERE supplier_id not in 
(select supplier_id from orders) 

SELECT *FROM suppliers
WHERE not EXISTS
(select *    from orders    where suppliers.supplier_id = orders.supplier_id)

SELECT * FROM suppliers
left join orders on suppliers.supplier_id = orders.supplier_id
where orders.supplier_id is null


2.3 当条件字段为NULL的情况下 
但存在NULL的情况用not EXISTS和not in 查询结果不一致

NULL的特殊性:
在SQLServer中有三种状态 True False 和 Unknown三种
任何NULL的比较(大于 等于 小于)操作都是Unknown状态
比如 a>NULL a=NULL NULL=NULL都是Unknown状态，所有的查询
条件(ON, WHERE, and HAVING)都是将Unknown状态当做False,
相反在所有的数据库约束都将Unknown状态看做True
比如 GROUP BY clause groups all NULLs into one group.
因为not in 是一次执行的查询 而 Not EXISTS是循环执行外表的查询
单一次执行的时候和NULL比较那查询条件的结果就是Unkown了，也就称了False了
因此没有结果
相反 Not EXISTS是循环执行的， 所以避免了和NULL的比较


需要not in语句的添加where条件 选取字段 is not null
CREATE TABLE [dbo].[tx1](
[c1] [int] NULL,
[c2] [int] NULL
) 
CREATE TABLE [dbo].[tx2](
[c1] [int] NULL,
[c2] [int] NULL
)

insert into [tx1]([c1],[c2]) values(1,2)
insert into [tx1]([c1],[c2]) values(1,3)

insert into [tx2]([c1],[c2]) values(1,2)
insert into [tx2]([c1],[c2]) values(1,null)

select * from [tx1] where c2 not in(select c2 from [tx2])
没有返回结果
分析：c2 not in(2,NULL) ==> c2=2 or c2=NULL true or Unknow ==>Unknow ==>False
所以没有返回结果

select * from [tx1] where not exists (select c2 from [tx2] where tx1.c2 = tx2.c2 )
输出结果：1,2
not exists是循环执行的
他首先执行select c2 from [tx2] where tx1.c2 = 2 
返回结果，因此不做任何输出
然后执行select c2 from [tx2] where tx1.c2 = 3 ，没有返回任何结果
所以not exists 操作后查询条件为True,然后输出本条语句

3 不同的查询的性能比较
3.1 常用排查性能的方法
主要是看SQL的执行计划，比较性能消耗
比较性能的方法：
set statistics io on
set statistics time on
查询执行计划
查询执行过的SQL的执行计划：
select st.text,qp.query_plan,cp.* from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(plan_handle) st
                cross apply sys.dm_exec_query_plan(plan_handle) qp where text like '% %'
读取现有的SQL的执行计划
SET SHOWPLAN_XML ON 
3.2 In,Exists和join的逻辑执行过程
3.2.1 in操作
select * from t1 where x in ( select y from t2 ) 
    事实上可以理解为： 
    select *       from t1, ( select distinct y from t2 ) t2 
     where t1.x = t2.y; 
实际和Join操作一样
3.2.2 exists操作
select * from t1 where exists ( select null from t2 where y = x ) 
    可以理解为： 
    for x in ( select * from t1 ) 
    loop 
       if ( exists ( select null from t2 where y = x.x ) 
       then 
          OUTPUT THE RECORD! 
       end if 
    end loop 
差异: exists   stops processing after the condition is met while join and in have to go over the whole table.
3.2.3 Join的操作
Join 的操作和In在大部分情况下是一样的，但是跟利于优化和理解
3.3 In,Exists和join的性能比较

3.2.1 当两个表都有合适的索引并且查询条件返回的结果集都比较小的情况下：
SQLServer对以上情况都使用了一个查询计划，性能差异不大， 
如果索引建的好，数据量不大一般使用 clustered index Seek 表关联用了NESTLOOP 
主要的差异是在在未对表进行分析前(第一次执行)。 推荐使用 Inner join
3.2.2 当查询的结果量比较大的时候
    若两个表数据量差异很大,则外层表是大表时使用IN较快,因为In一般使用的是Hash Join或者Merge Join
    外层表是小表时使用EXISTS较快;因为EXISTS使用的是NestLoop
若两表数据量接近,则使用IN较快;
     

3.3 not In,not Exists和left join的性能比较
情况和 In,Exists和join 一样 推荐使用left Join，在某些情况下可以使用not Exists
   
   
   
参考:
    
[url]http://blog.chinaunix.net/u2/78491/showart_1851896.html[/url]
[url]http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0[/url],289625,sid41_gci1072669,00.html
[url]http://www.techonthenet.com/sql/exists.php[/url]