ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PL/SQL高级应用(2)-exists和not exists的使用-ZT

PL/SQL高级应用(2)-exists和not exists的使用-ZT

原创 Linux操作系统 作者:spritesong 时间:2009-03-29 22:37:07 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 2.existsnot exists的使用

2.1. exists的使用

Exists用于只能用于子查询,可以替代in,若匹配到结果,则退出内部查询,并将条件标志为true,传回全部结果资料,in不管匹配到匹配不到都全 部匹配完毕,使用exists可以将子查询结果定为常量,不影响查询效果,而且效率高。如查询所有销售部门员工的姓名,对比如下:

IN is often better if the results of the subquery are very small

When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query.

When you write EXISTS in a where clause, you're telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query.

In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.

 

 

Inexists对比:

若子查询结果集比较小,优先使用in,若外层查询比子查询小,优先使用exists。因为若用in,则oracle会优先查询子查询,然后匹配外层查询,若使用exists,则oracle会优先查询外层表,然后再与内层表匹配。最优化匹配原则,拿最小记录匹配大记录。

使用in

select last_name, title

        from s_emp

        where dept_id in

                (select id

                from s_dept

                where name='Sales');       

 

使用exists

select last_name,title

       from s_emp e

       where  exists

       (select 'x' --把查询结果定为constant,提高效率

        from s_dept s where s.id=e.dept_id and s.name='Sales');

2.2 not exists的使用

        exists 含义相反,也在子查询中使用,用于替代not in。其他一样。如查询不在销售部的员工姓名

select last_name,title

       from s_emp e

       where  not exists

       (select 'x' --把查询结果定为constant,提高效率

        from s_dept s where s.id=e.dept_id and s.name='Sales');

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/293106/viewspace-580566/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-28

  • 博文量
    62
  • 访问量
    24836