in与exists语句相关的问题

一. In&Exists:

1. IN 语句

select a.*

  from a

 where a.cust_id in (select b.cust_id from b);

in适用: 当b集合小, a集合大的情况.

======================================================

小&大是个数量集的概念, 如小为1万, 大为100万+

大&小是以已知索引条件过滤后的数量级(或可针对条件即将建立的索引).而不是实体表a,b的数据集.

如

select *

  from orders o

 where order_date between to_date('2012-01-01','yyyy-mm-dd') 

                      and to_date('2012-01-31 23:59:59','yyyy-mm-dd hh24:mi:ss');

如order_date上有索引,结果集为1月份记录行数. 而不是order表的订单行数.

======================================================

 

2. Exists语句

select a.*

  from a

 where exists (select 1                 

                      from b 

                    where b.cust_id = a.cust_id);

Exist语句适用:a集合小, b集合大

 

3.  当a,b均为小集合时, in语句更好.

   

4. 当使用join语句, 对查询结果集无影响时, 应该 使用join语句. (比如a,b表之间有外键, 且外键列上有非空限制,肯定应该使用join)

select o.*

  from order o

  join customers c on c.cust_id = o.cust_id

  

5. 当a,b均为大集合. 修改业务或在a,b上使用索引条件,至少使其中之一小结果集. 

 

主结果集	子查询结果集	使用
大	小	IN
小	大	Exists
小	小	IN语句更好些
大	大	改业务
Join不影响结果集		Join

 

二. Not in & Not exists:

允许出现的情况:not in ('a','b')

not in (select cust_id from b) --要非常小心的使用

原则上使用not exists是不允许的, 一般使用 集合操作差集(minus) 来实现. 尤其是多次not exists

select a.*

  from a

 where not exists (select 1 from b where a.cust_id = b.cust_id)

   and not exists (select 1 from c where a.cust_id = c.cust_id);

 

select *

from a

where cust_id in

( 

select cust_id

  from a

  minus 

( select cust_id

    from b

   union all

  select cust_id

    from c)

);

 

三. update&delete子句与 in&exists子句的组合使用. 原则和上面的原则一样. update&delete的表即为主表.

update a

    set comments = 'test'

 where a.cust_id in (select b.cust_id from b);