`

EXISTS、IN、NOT EXISTS、NOT IN的区别

阅读更多
EXISTS、IN、NOT EXISTS、NOT IN的区别(ZT)


EXISTS、IN、NOT EXISTS、NOT IN的区别:


in适合内外表都很大的情况,exists适合外表结果集很小的情况。
exists 和 in 使用一例
===========================================================
今天市场报告有个sql及慢,运行需要20多分钟,如下:
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where exists(
select 1
from (
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = 'sssssssssssssssss'
) a
where a.decl_no = cd.decl_no
and a.goods_no = cd.goods_no
)
上面涉及的3个表的记录数都不小,均在百万左右。

exists和in的区别:
in 是把外表和那表作hash join,而exists是对外表作loop,每次loop再对那表进行查询。
这样的话,in适合内外表都很大或者外表大而内表小的情况,exists适合外表结果集很小的情况。


我们先讨论IN和EXISTS。
    select * from t1 where x in ( select y from t2 )
    事实上可以理解为:
    select *
      from t1, ( select distinct y from t2 ) t2
     where t1.x = t2.y;
    ——如果你有一定的SQL优化经验,从这句很自然的可以想到t2绝对不能是个大表,因为需要对t2进行全表的“唯一排序”,如果t2很大这个排序的性能是不可忍受的。但是t1可以很大,为什么呢?最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想,如果t1.x和t2.y 都有索引,我们知道索引是种有序的结构,因此t1和t2之间最佳的方案是走merge join。另外,如果t2.y上有索引,对t2的排序性能也有很大提高。
    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
    ——这个更容易理解,t1永远是个表扫描!因此t1绝对不能是个大表,而t2可以很大,因为y=x.x可以走t2.y的索引。



而我目前的情况适合用in来作查询,于是我改写了sql,如下:
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where (decl_no,goods_no) in
(
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = ‘ssssssssssss’
)

让市场人员测试,结果运行时间在1分钟内。问题解决了,看来exists和in确实是要根据表的数据量来决定使用。

请注意not in 逻辑上不完全等同于not exists,如果你误用了not in,小心你的程序存在致命的BUG:


请看下面的例子:
create table t1 (c1 number,c2 number);
create table t2 (c1 number,c2 number);

insert into t1 values (1,2);
insert into t1 values (1,3);
insert into t2 values (1,2);
insert into t2 values (1,null);

select * from t1 where c2 not in (select c2 from t2);
no rows found
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
c1 c2
1 3

正如所看到的,not in 出现了不期望的结果集,存在逻辑错误。如果看一下上述两个select语句的执行计划,也会不同。后者使用了hash_aj。
因此,请尽量不要使用not in(它会调用子查询),而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值,则查询将不返回任何记录,正如上面例子所示。
除非子查询字段有非空限制,这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或merge_aj连接。


根据如下表的查询结果,那么以下语句的结果是(知识点:not in/not exists+null)
SQL> select * from usertable;
USERID           USERNAME
-----------      ----------------
      1          user1
      2          null
      3          user3
      4          null
      5          user5
      6          user6
     
SQL> select * from usergrade;
USERID         USERNAME           GRADE
----------     ----------------   ----------
      1        user1              90
      2        null               80
      7        user7              80
      8        user8              90
执行语句:
select count(*) from usergrade where username not in (select username from usertable);

select count(*) from usergrade g where not exists
(select null from usertable t where t.userid=g.userid and t.username=g.username);

结果为:语句1(   )   语句2  (   )

A: 0     B:1     C:2     D:3      E:NULL
.--------------------------------------------------------------------------------

现在终于搞清楚了,答案这里也不说了。就说说几个知识点
1、NULL不是没有,而是不知道。在oracle排序中它代表最大的值。

10:55:32 SQL> create table li2_tmp as select 1 a,'this is 1' b from dual;
Table created.
10:56:35 SQL> insert into li2_tmp (a,b) values (null,'this is null');
10:56:55 SQL> insert into li2_tmp (a,b) values (null,'it'' null');
1 row created.
10:57:32 SQL> commit;
Commit complete.
10:57:37 SQL> select * from li2_tmp where a>0;

         A B
---------- ---------
         1 this is 1

Elapsed: 00:00:00.00
10:58:02 SQL> select * from li2_tmp order by a;

         A B
---------- ---------
         1 this is 1
           it' null

11:02:51 SQL> select * from li2_tmp where a=null;

no rows selected

说明:NULL不能用于比较,因为它的值谁都不知道,但在排序了,oracle 认为它是最大的值。

2、什么东西都不在NULL中,也不在非空中
这个理解起来很困难,还是用例子吧
10:58:21 SQL> select count(*) from li2_tmp where a in (null);

  COUNT(*)
----------
         0

11:02:34 SQL>  select count(*) from li2_tmp where a not in (null);

  COUNT(*)
----------
         0

11:10:42 SQL> select * from li2_tmp where exists (select null from dual);

         A B
---------- ---------
         1 this is 1
           it' null

Elapsed: 00:00:00.00
11:11:16 SQL> select * from li2_tmp where not  exists (select null from dual);

no rows selected

11:13:42 SQL> select * from li2_tmp t1 where exists (select null from li2_tmp t2 where t1.a=t2.a)
11:14:26 SQL> /

         A B
---------- ---------
         1 this is 1

分享到:
评论

相关推荐

    sql case when exists not exists in not in

    在SQL查询中,`CASE WHEN`、`EXISTS`、`NOT EXISTS`以及`IN`和`NOT IN`是常用的操作符,它们用于处理复杂的条件判断和数据筛选。这些概念对于理解和编写高效的SQL语句至关重要,尤其是在数据分析和数据库管理中。 `...

    sql in,exists,not in,not exists区别

    SQL 中 IN、EXISTS、NOT IN、NOT EXISTS 的区别 IN、EXISTS、NOT IN、NOT EXISTS 是 SQL 中四种常用的条件判断运算符,它们之间的区别主要体现在使用场景、执行效率和语法结构上。 IN IN 是一种条件判断运算符,...

    经典SQL查询总结关于Exists,not Exists.in ,not in效率的说明。

    ### 经典SQL查询总结关于Exists, not Exists, IN, not IN 效率的说明 在数据库查询操作中,存在着多种方法来实现相似的功能,但不同的实现方式在性能上可能会有显著差异。本文将深入探讨 SQL 中 `EXISTS`, `NOT ...

    sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句

    SQL语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句 SQL语句优化是数据库性能优化的重要方面之一。在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下,使用EXISTS...

    exists 和 not exists的详细解释

    exists 和 not exists的详细解释

    SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt

    SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载!

    EXISTS_和_NOT_EXISTS

    - 效率:在某些情况下,`EXISTS` 和 `NOT EXISTS` 可能会提供相似的性能,但通常 `IN` 和 `NOT IN` 比较操作符在处理大量数据时更有效。然而,当涉及到子查询时,`EXISTS` 和 `NOT EXISTS` 可能更合适,因为它们只...

    NOT EXISTS ⇔ NOT IN ⇒ NOT NULL 问题 结果不一样 疑问 如果等效

    通过以上分析可以看出,虽然 `NOT EXISTS`、`NOT IN` 和 `NOT NULL` 在表面上看似相似,但它们之间存在着明显的区别,尤其是在处理 `NULL` 值时。因此,在实际应用中,应根据具体需求选择合适的操作符以达到最佳效果...

    sql exists和not exists用法

    相较于`IN`、`NOT IN`等操作,`EXISTS`与`NOT EXISTS`具有更高的效率,尤其是在处理大型数据集时。 #### EXISTS 介绍 `EXISTS`关键字用于检查子查询是否至少返回一行数据。如果子查询返回至少一行数据,则`EXISTS`...

    MySQL中的in,exists,not in,not exists查询过程对比及结论

    MySQL优化之in,exists,not in,not exists的区别in与existsin查询过程结论:exists查询过程:结论:not in与not existsnot in查询过程:结论:not exists查询过程:结论: 首先我们使用两个用户表作为实例 insert ...

    exists SQL用法详解、exists和not exists的常用示例

    `EXISTS`和`IN`的区别在于,`IN`通常用于比较单个字段值,而`EXISTS`可以处理更复杂的查询,它关心的是子查询是否返回行,而不关心返回的具体值。`IN`通常用于获取特定值列表内的记录,而`EXISTS`则可以用于检查记录...

    SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句2.txt

    SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句2.txt,欢迎下载!

    INEXISTS与NOT INNOT EXISTS 的优化原则小结

    在SQL查询优化中,`IN`、`EXISTS`、`NOT IN`和`NOT EXISTS`是四个常见的比较和过滤条件,它们在不同场景下有不同的性能表现。以下是对这些操作符优化原则的详细说明: 1. **EXISTS的执行流程**: `EXISTS` 子查询...

    Oracle In和exists not in和not exists的比较分析

    在Oracle数据库中,`IN`、`EXISTS`、`NOT IN` 和 `NOT EXISTS` 是四个常用的子查询操作符,它们在SQL查询语句中扮演着不同的角色,且各有其性能特点。以下是对这些操作符的详细分析和比较。 1. `IN` 操作符: `IN` ...

    in exist not_in

    IN、EXISTS、NOT EXISTS、NOT IN 在 SQL 语句中的应用和区别 IN 语句和 EXISTS 语句都是 SQL 语句中用来判断是否存在某个值的语句,但是它们的实现机制和应用场景是不同的。 IN 语句是通过 hash 连接来实现的,它...

    基本数据插入 except和intersect和exists和not exists和union和union all

    基本数据插入 except和intersect和exists和not exists和union和union all sql server

    oracle中exists_和in的效率问题详解

    Oracle 中 EXISTS 和 IN 的效率问题详解 EXISTS 和 IN 都是 Oracle 中的集合操作符,但它们在使用和执行效率上有所不同。本文将深入探讨 EXISTS 和 IN 的使用场景、执行机制和效率问题。 EXISTS 的使用场景和机制 ...

    QL中exists 与 in_的比较

    在实际应用中,选择`EXISTS`还是`IN`,以及是否使用`NOT EXISTS`代替`NOT IN`,应当基于对数据集大小、表结构、索引状况以及查询需求的深入理解。正确选择可以显著提升查询效率,避免不必要的资源消耗。

    IN&EXISTS与NOT IN&NOT EXISTS 的优化原则小结

    4. `NOT IN`与`NOT EXISTS`的区别: - `NOT IN`不能与`NOT EXISTS`完全互换,特别是在处理可能包含`NULL`值的列时。例如,`NOT IN`会排除所有包含`NULL`的匹配,而`NOT EXISTS`则不会。 - 当主查询表较大,子查询...

Global site tag (gtag.js) - Google Analytics