Oracle笔记之any,some,all以及exists与in的用法

any 、 some 、 all 区别

any表示子查询返回值中的任意一个值，相当于n个or的语句

    >any(单列多行子查询)， 也就是只要大于子查询最小返回值就可以了

some表示满足其中一个。 some和any用法相同。

all表示子查询返回值中的所有值，相当于n个and语句

   >all(单列多行子查询)，意思是必须要大于子查询中最大返回值才行

 

 

 

in : 常用匹配多个定值中其中的一个，效率，子查询的记录数较少时可以 使用 in
       in (1,2,3,4)  相当于or
 
 where(结果集){
    if(deptno =20){
       print();
    }
    if(deptno =10){
       print();
    }
    if(deptno =30){
       print();
    }
    if(deptno =40){
       print();
    }
 }

 

查询工资大于1500 且 and 含有佣金的人员姓名
select ename, sal
  from emp
 where (empno, sal) in (select empno, sal
                          from emp
                         where comm is not null
                           and sal > 1500)

in是把外表和内表做了hash连接，内表返回记录过多，会产生临时表，效率低
  
 exists 子查询的条件为真，获取记录，在子查询的记录数较多时或者存在区间区间时 使用
 exists (子查询存在记录)
 可以理解while为一个循环，如下：
 while(外层结果集){
        boolean flag =false;
        while(内层结果集){
                if(条件满足){                   
                     flag =true;
                     break;                   
                }
        }
        if(flag){
             print(匹配结果);   
        }
 }

 

求工资在500到1500之间，部门编号为20或者30的员工信息

select ename, empno, deptno, sal
  from emp a
 where exists (select 1
          from emp b
         where a.sal between 500 and 1500
           and a.deptno in (20, 30)
           and a.empno = b.empno)
这种查询时有区间的要求，用exists，in不适合

 

 

求奖金非null的员工信息，错误例子：

select ename, comm, sal, deptno

  from emp e1

 where  exists (select 1

          from emp e2

         where e2.comm is not null

           and e2.deptno = e1.deptno);

exists首先执行外层查询，在执行内层查询，从e2表找到了comm非null，并且depno等于e1的deptno的结果集，flag为true，跳出内层循环，匹配结果集时，匹配的是e1的deptno的相应结果集

而deptno（部门号）对应有多个员工，外层查询到的结果则是相应部门号的所有员工。不符要求。

 

正确写法：

select ename, sal

  from emp a

 where  not  exists (select 1

          from emp b

         where comm is null

           and a.empno = b.empno) 

exists首先执行外层查询，再执行内层查询，从e2表找到了comm非null，并且depno等于e1的deptno的结果集，flag为true，跳出内层循环，匹配结果集时，匹配的是e1的empno相应的结果集

于是，外层查询到的结果是相应员工号的员工信息，员工号对应员工的信息是唯一确定的。符合。

 

exists首先执行外层查询，再执行内层查询，与IN相反