Oracle数据库之SQL子查询详解

 前言

               考虑到这样一种情况:查询员工中有哪些员工的工资比SMITH高。要解决这类问题我们首先要

         要的就是将SIMTH的工资查询出来，之后再将所有员工的工资与其进行比较。那么如何利用第一

         次查询的结果了，就需要用到子查询了。

    子查询

             子查询其实就是指嵌入到其他语句中的select语句，也称其为嵌套查询。值得注意的的在DDL

        语句中应用子查询的时候子查询可以使用order by 子句。但是在DML语句中的where子句，set子句

        中，子查询是不能使用order by子句的。

            简单例子：查询工作和SMITH一样的所有员工的信息（不包含SMITH）

[sql]  view plain  copy

1. select ename ,sal from emp e where job =   
2.    (select job from emp where ename = 'SMITH')   
3.     and e.ename <> 'SMITH';  

    子查询分类

        单行子查询

                 只返回一条记录的子查询 ，就可上述例子一样 

[sql]  view plain  copy

1. --返回工资比SMITH多的员工和工资情况  
2. select ename ,sal from emp where  
3.   sal>  
4.    (  
5.    select sal from emp where ename ='SMITH'  
6.    ) ;  

                 能够用于当行子查询的操作符有>、<、=、like等等

        多行子查询

              多行子查询返回的是一个记录组，也就是多条记录，这种情况对于子查询的操作就

             应该是in、any、all，exits 等。直接一个实例来看

                 查询各部门中员工工资最高的员工信息

             一种看起来是正确的查询方式：

[sql]  view plain  copy

1. select max(sal) from emp group by(deptno)  --子查询的结果  
2.   select ename ,sal ,job from emp   
3.    where sal in  
4.    (  
5.       select max(sal) from emp group by deptno  
6.    )  
7.   ;  

                不过仔细思考就可以知道上述思路是存在问题的，因为虽然将各部门中的最高工资查询出来了

          但是外部查询中无法无法指定员工的工资与各自部门的最高工资进行比较。查询出来的结果必然

          是不正确的。

            下面提供一种正确的方法：  

[sql]  view plain  copy

1.  select ename ,deptno,sal,job from emp e  
2.    where not exists  
3.   （  
4.      select ename,deptno,job from emp  
5.      where deptno = e.deptno   
6.      and  
7.      sal > e.sal  
8.   ）   
9. order by deptno  
10. ;  

           其实这种子查询的方式是相关子查询的方式，至于什么是相关子查询会在后面进行介绍。

           多行子查询的一些操作符的使用示例：

                  IN操作符:

[sql]  view plain  copy

1. --in操作符的使用  
2. --查询员工的职业类型在部门10中有的员工的信息  
3. select ename,job,sal,deptno from emp where job   
4.  in   
5.  (  
6.    select distinct job from emp where deptno =10  
7.  )    
8. ;  

                  ALL操作符：

[sql]  view plain  copy

1. --ALL操作符的使用  
2. --查询员工的工资大于30号部门的所有员工的信息  
3. select ename,sal,deptno from emp   
4.   where sal > all  
5.   (  
6.     select sal from emp where deptno=30  
7.   )    
8. ;    
9. --">all"表示大于最大的，"<ALL"表示小于最小的    

                  ANY操作符：

[sql]  view plain  copy

1. --ANY操作符的使用   
2. --只要员工的工资存在这种情况：  
3. --即他的工资情况比30部门的其中一个人的工资多就查询出来  
4. select ename,sal,deptno from emp  
5.   where sal>any  
6.   (  
7.    select sal from emp where deptno = 30  
8.   )   
9. ;                            

            “>ANY”意味着大于最小值，"<any"意味着小于最大值，"=ANY"意味着in

         多列子查询

                指的是子查询语句返回的是多列。

                例如查询：某个名叫SMITH的员工，但是由于员工的名字可能出现重复，因此需要结合其工作

            和和部门id进行判断。

[sql]  view plain  copy

1. --多列子查询实例  
2.  select ename ,job ,deptno from emp  
3.    where (ename,job,deptno) =  
4.     (  
5.       select ename, job,deptno from emp   
6.        where ename='SMITH'   
7.      )  
8.  ;--这里主要在于说明多列子查询的使用，并不注重其实际意义  

                   ●   成对比较                 

[sql]  view plain  copy

1. --成对比较  
2. select ename ,sal,comm ,deptno from emp   
3.   where (sal,nvl(comm,-1)) in  
4.    (        
5.       select sal,nvl(comm,-1) from emp  
6.       where deptno = 30  
7.    )   

                  ●   非成对比较

[sql]  view plain  copy

1. --非成对比较  
2. elect ename ,sal,deptno,comm from emp  
3.  where sal in  
4.     (  
5.       select sal from emp   
6.       where deptno = 30  
7.     )                     
8.  and nvl(comm,-1) in  
9.    (  
10.       select nvl(comm,-1) from emp  
11.       where deptno = 30  
12.    )  
13.  ;  

              其他子查询

                      1、相关子查询

                            是指需要引用主查询表列的子查询 语句。相关子查询是通过exists 来实现的  。                

[sql]  view plain  copy

1. --查询在NEW YORK工作的员工的信息  
2. select ename ,job ,sal ,deptno from emp  
3.    where exists  
4.     (  
5.       select 1 from dept   
6.       where dept.deptno = emp.deptno   
7.       and dept.loc ='NEW YORK'  
8.     )  
9.  ;  

                    思考：相关子查询的执行过程：

                        相关子查询会引用外部查询的一列或多列，在执行的时候外部查询的每一行被一次一行的

                   传递给子查询，子查询依次读取外部查询传递过来的每一个值，并将其用到子查询上，直到外部

                   查询的所有行都用完为止，然后返回查询结果。

                   2、FROM子句中的子查询

                         在from子句中使用子查询的时候子查询会被当做一个view对待，需要给子查询指定别名。

[sql]  view plain  copy

1. --查看比本部门平均工资高的员工信息  
2.    select ename,job,sal from emp ,  
3.      (  
4.         select deptno,avg(sal) as avgsal from emp  
5.         group by deptno   
6.      ) tmp_dept  
7.    where emp.deptno=tmp_dept.deptno and sal >tmp_dept.avgsal;  

                  3、DML语句中使用子查询

[sql]  view plain  copy

1.             --DML语句中使用子查询  
2.             --insert中  
3.               insert into employee (id,name,title,salary)  
4.                select emptno，ename ,job,sal from emp;  
5.             --update中  
6.               update emp set (sal,comm)=  
7.                 (  
8.                   select sal,comm from emp  
9.                   where ename ='SMITH'  
10.                 )          
11.               where job =   
12.                 (  
13.                   select job from emp where ename ='SMITH'  
14.                 )  
15.              ;  
16.             --去除销售部门  
17.               delete from emp where deptno =  
18.                 (  
19.                   select deptno from dept  
20.                   where dname ='SALES'  
21.                 )  
22.               ;  

                 4、在DDL语句中使用子查询

                      通过select子查询来实现创建视图的方式来说明其使用方法

                      需要注意的是在执行下面的sql语句之前，要对scott用户赋予创建视图的权利

[sql]  view plain  copy

1. SQL>conn / as sysdba  
2. SQL>grant create view to scott;              

[sql]  view plain  copy

1. --创建视图并查询视图    
2. create or replace view dept10 as  
3.   select empno,ename ,job,sal,deptno from emp  
4. where deptno = 10 order by empno;  
5.  select * from dept10;  

                      关于子查询的相关信息就到这里了。