`
tottichen
  • 浏览: 13513 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
最近访客 更多访客>>
社区版块
存档分类
最新评论

EXISTS、IN、NOT EXISTS、NOT IN的区别与性能分析

SQL 
阅读更多
1.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
对于in 和 exists的性能区别:
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了

另外IN时不对NULL进行处理
如:
select 1 from dual where null in (0,1,2,null)
为空
2.NOT IN 与NOT EXISTS:
NOT EXISTS的执行流程
select .....
from rollup R
where not exists ( select 'Found' from title T
where R.source_id = T.Title_ID);
可以理解为:
for x in ( select * from rollup )
loop
if ( not exists ( that query ) ) then
OUTPUT
end if;
end;

注意:NOT EXISTS 与 NOT IN 不能完全互相替换,看具体的需求。如果选择的列可以为空,则不能被替换。

例如下面语句,看他们的区别:
select x,y from t;
x y
------ ------
1 3
3 1
1 2
1 1
3 1
5
select * from t where x not in (select y from t t2 )
no rows

select * from t where not exists (select null from t t2
where t2.y=t.x )
x y
------ ------
5 NULL
所以要具体需求来决定

对于not in 和 not exists的性能区别:
not in 只有当子查询中,select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大,子查询中的表小但是记录多,则应当使用not in,并使用anti hash join.
如果主查询表中记录少,子查询表中记录多,并有索引,可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null
NOT IN 在基于成本的应用中较好

比如:
select .....
from rollup R
where not exists ( select 'Found' from title T
where R.source_id = T.Title_ID);

改成(佳)

select ......
from title T, rollup R
where R.source_id = T.Title_id(+)
and T.Title_id is null;

或者(佳)
sql> select /*+ HASH_AJ */ ...
from rollup R
where ource_id NOT IN ( select ource_id
from title T
where ource_id IS NOT NULL )

讨论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/EXISTS的讨论,我们可以得出一个基本通用的结论:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
我们要根据实际的情况做相应的优化,不能绝对的说谁的效率高谁的效率低,所有的事都是相对的
分享到:
评论

相关推荐

    sql case when exists not exists in not in

    而`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效率的说明。

    通过对比分析 `EXISTS`, `NOT EXISTS`, `IN`, `NOT IN` 以及不同类型的连接操作,我们可以发现不同的查询策略在不同的应用场景下具有不同的优劣性。选择合适的查询方式不仅能够提高查询效率,还能够节省系统资源,...

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

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

    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`...

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

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

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

    与 `NOT IN` 相比,`NOT EXISTS` 可以利用索引来提高查询性能。例如: ```sql SELECT * FROM employees WHERE NOT EXISTS (SELECT 1 FROM departments WHERE departments.id = employees.department_id AND city = '...

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

    3. **NOT IN与NOT EXISTS**: - `NOT IN` 的执行效率往往取决于子查询结果集的大小。如果子查询返回大量记录,且主查询的表较大,使用`NOT IN`结合反连接或`/*+ HASH_AJ */`提示可能会更优。 - `NOT EXISTS` 在...

    QL中exists 与 in_的比较

    在数据库查询语言QL中,`exists`与`in_`(通常写作`IN`)是两种常见的用于子查询的语法结构,它们各自有着独特的应用场景和性能表现。理解这两种语法的区别对于优化查询效率至关重要。 ### `exists`与`in_`的基本...

    mysql多表查询和EXISTS查询性能对比

    NOT EXISTS 是另一种排除条件查询的方式,其逻辑与NOT IN类似,但在某些情况下性能更优。NOT EXISTS 的SQL语法如下: ```sql SELECT * FROM tableA WHERE NOT EXISTS (SELECT 1 FROM tableB WHERE tableA.id = ...

    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,欢迎下载!

    in exist not_in

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

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

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

    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