SQL优化之 not in

not in

select * from dic_region_old a where a.region_code not in (select b.region_code from dic_region b) 

PL/SQL  执行 选择17 行 耗时14.312秒

用外连接和is null

select a.* from dic_region_old a,dic_region b
where a.region_code = b.region_code(+)
and b.region_code is null

PL/SQL  执行 选择17 行 耗时0.031~0.047秒

用 not exist

select * from dic_region_old a
where not EXISTS 
(select b.* from dic_region b
where a.region_code = b.region_code)

PL/SQL  执行 选择17 行 耗时0.031~0.047秒

如果有其它限制条件 只能 用 not exist

下面是转载内容

用外连接和is null优化not in的限制条件

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

作者: 西门吹牛(http://.itpub.net)
发表于: 2005.01.06 14:50
分类: 数据库优化
出处: http://.itpub.net/post/306/12926
---------------------------------------------------------------

用外连接和is null优化not in的限制条件

今天帮同事优化一个SQL语句
select *
from tbl_st1000_embedlist
where to_char(ACCDATE,'yyyy.mm.dd')='2005.01.01' and
   sublog_id not in
   ( select sublog_id
     from tbl_embedlist
    where to_char(ACCDATE,'yyyy.mm.dd')='2005.01.01'
   )
上面的SQL语句查询到有很多记录。

我开始觉得很简单，这不就是用外连接和is null优化not in的SQL语句吗，
于是我写成下面的结果

select a.*
from tbl_st1000_embedlist a, tbl_embedlist b
where a.sublog_id = b.sublog_id(+)
 and to_char(a.ACCDATE,'yyyy.mm.dd')='2005.01.01'
 and to_char(b.ACCDATE,'yyyy.mm.dd')='2005.01.01'
 and b.sublog_id is null;
 
但是一条记录也没有得到，后来我修改了SQL语句，用not exists替换not in
select *
from tbl_st1000_embedlist a
where to_char(ACCDATE,'yyyy.mm.dd')='2005.01.01'
  and not exists
   ( select 'X'
     from tbl_embedlist
    where to_char(ACCDATE,'yyyy.mm.dd')='2005.01.01'
    and sublog_id = a.sublog_id
   )

这样才得到正确的结果。

我分析了一下，如果没有其他的限制条件，用外连接和is null可以优化not in的SQL语句，但是如果有了其他的限制条件
就不能这样用了。

原因如下：用外连接和is null替换not in的SQL语句，例如 a not in b ,可以理解为从A找不在B的记录，
如果在增加了其他的限制条件，这些其他的限制条件就会和外连接联合起作用，把is null这个限制条件理解为某个字段为空，而不是没有这样的记录了

西门吹牛 发表于:2005.01.06 14:50 ::分类: ( 数据库优化 ) ::阅读:(301次) :: 评论 (0)