exists 和 with

exists查询

使用EXISTS语句可以测试集合是否为空，EXISTS语句通常与子查询结合在一起使用。只要子查询中至少返回一个值，则EXISTS语句的值就为True。\

查到就不再继续查

找出领导 此员工的empno在mgr列存在即是领导

SCOTT@ora10g> select empno,ename,mgr from emp e where exists (select 1 from emp where mgr=e.empno);

     EMPNO ENAME      MGR

---------- ---------- ----------

      7902 FORD      7566

      7698 BLAKE     7839

      7839 KING

      7566 JONES     7839

      7788 SCOTT     7566

      7782 CLARK     7839

6 rows selected.

in也能做到 只是in的效率不高 in会一直比下去 而exists比成功就不在比了.

SCOTT@ora10g> select empno,ename,mgr from emp where empno in (select mgr from emp);

     EMPNO ENAME      MGR

---------- ---------- ----------

      7902 FORD      7566

      7698 BLAKE     7839

      7839 KING

      7566 JONES     7839

      7788 SCOTT     7566

      7782 CLARK     7839

6 rows selected.

SCOTT@ora10g> 

取反 

用in改写时并不一定得到和not exists一致的结果

即是查找普通员工 不是领导的人

SQL> select empno,ename from emp e where not exists (select 1 from emp where mgr=e.empno);

     EMPNO ENAME

---------- ----------

      7844 TURNER

      7521 WARD

      7654 MARTIN

      7499 ALLEN

      7934 MILLER

      7369 SMITH

      7876 ADAMS

      7900 JAMES

8 rows selected.

SQL> 

SQL> select empno,ename from emp e where empno in (select mgr from emp);

     EMPNO ENAME

---------- ----------

      7902 FORD

      7698 BLAKE

      7839 KING

      7566 JONES

      7788 SCOTT

      7782 CLARK

6 rows selected.

SQL> select empno,ename from emp e where empno not in (select mgr from emp);

no rows selected

SQL> 原因是什么?

select 

with 语句

当查询中多次用到某一部分时，可以用with语句创建一个公共临时表。

因为子查询在内存临时表中，避免了重复解析，所以执行效率会提高不少。

临时表在一次查询结束自动清除。

语法:

一个查询结果 在一条SQL中多次调用时

可以使用with将执行结果缓存 后期再使用 直接从缓存中获取 不需要执行

with

   alias_name1 as    (subquery1),  

   alias_name2 as    (subQuery2),

   ……,

   alias_nameN as    (subQueryN)

   select col1,col2…… col3  

   from alias_name1,alias_name2……,alias_nameN 

例子:

SQL> with

    q1 as (select 3+5 s from dual),

    q2 as (select 3*5 m from dual),

    q3 as (select s,m,s+m,s*m from q1,q2)

    select * from q3;

 S    M      S+MS*M

---------- ---------- ---------- ----------

 8   15       23120

SQL> 

with子句中的视图叫做询问块，询问块的复杂查询在

with子句中只运行一次，运行成功后会将询问块的结果集

保存到temp表空间，以后再次调用询问块时会在后台转换

为对结果集的直接访问