六、子查询

1.子查询导入

--查询工资比SCOTT高的员工信息

--第1步. 得到SCOTT的工资

SQL> select sal from emp where ename='SCOTT';

-->结果：3000

--第2步. 查询比3000高的员工信息

SQL> select * from emp where sal > 3000;

-->结果：

分两步，查询工资比SCOTT高的员工信息

--子查询解决的问题： 不能一步求解的问题

SQL> select * from emp

          where sal > (select sal from emp

                              where ename='SCOTT');

-->结果：和上面的一致

2.oracle中子查询需要注意的问题

（1）将子查询放入括号中

（2）采用合理的书写风格

（3）可以在主查询的where,select,from,having后面放子查询

（4）不可以在朱查询的group by 的后面放子查询

（5）单独强调from后面放置子查询

（6）主查询和子查询可以不是同一张表，只要子查询返回的结果主查询可以使用即可

（7）一般来讲，不在子查询中使用order by;但在top n分析问题中，必须使用order by

（8）单行子查询只能使用单行子查询，多行子查询只能使用多行子查询

（9）子查询中的null值问题

（10）一般先执行子查询，再执行主查询；但相关子查询除外

3.oracle子查询距离

--（3）可以在主查询的where select from having 后面放置子查询

--在select后的子查询只能是单行子查询

SQL>select ename,sal,(select job from emp where empno=7839) from emp;

-->结果：

在select后的子查询只能是单行子查询

--（5）单独强调from后面放置子查询（可以看做一张表）

--查询员工的姓名和薪水

SQL> select *

          from (select ename,sal

                    from emp);

-->结果：

from后面放置子查询（可以看做一张表）

-- （6）主查询和子查询可以不是同一张表，只要子查询返回的结果主查询可以使用即可

--查询部门名称为SALES的员工信息

SQL> select *

          from emp

          where deptno=(select deptno

                                  from dept

                                  where dname='SALES');

===》等价于

SQL> select e.*

          from emp e,dept d

          where e.deptno=d.deptno and d.dname='SALES';

-->结果：

主查询和子查询可以不是同一张表

补：单行子查询

      1.只返回一行的子查询叫单行子查询。

      2.单行子查询使用单行比较操作符。

单行子查询只能使用单行比较操作符

      3.单行子查询只能使用单行操作符，而=和！=对null无效，因此如果子查询返回空值，就查不到任何数据。

--多行子查询：in 在集合中，不要使用not in，或在使用前确保没有null值

--查询部门名称为SALES和ACCOUNTING的员工信息

SQL> select *

          from emp

          where deptno in (select deptno

                                      from dept

                                      where dname='SALES' or dname='ACCOUNTING');

===》等价于：

SQL> select e.*

          from emp e,dept d

          where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');

-->结果：

多行子查询

补：多行子查询

      1.返回多行结果的子查询叫多行子查询。

      2.使用多行比较操作符。（注意没有not in,因为多行子查询可能会返回null值，而前面说过，有null值，就能用not in下面的第3条补充阅读，应该可以解决疑惑）

多行子查询使用多行比较操作符

       3.关于null为什么不能使用not in的说明：

Multiple-Row Subqueries

Subqueries that return more than one row are called multiple-row subqueries. You use a multiple-row operator, instead of a single-row operator, with a multiple-row subquery. The multiple-row operator expects one or more values.

   SELECT last_name, salary, department_id

   FROM   employees

   WHERE  salary IN (SELECT   MIN(salary)

                     FROM     employees

                     GROUP BY department_id);

Example

Find the employees who earn the same salary as the minimum salary for each department.

The inner query is executed first, producing a query result. The main query block is then processed and uses the values returned by the inner query to complete its search condition. In fact, the main query would appear to the Oracle server as follows:

   SELECT last_name, salary, department_id

   FROM   employees

   WHERE  salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);

补：sql优化：

        4.如果多表查询和子查询都可以解决问题，理论上应尽可能使用多表查询。因为子查询会对数据库操作多次

--any：和集合的任意一个值比较

--查询工资比30号部门任意一个员工高的员工信息（比最小值大就行）

SQL> select *

          from emp

          where sal > any (select sal from emp where deptno=30);

===》等价于

SQL> select *

          from emp

          where sal > (select min(sal) from emp where deptno=30)

-->结果：

any，和集合的任意一个值比较

--all：和集合的所有值比较

--查询工资比30号部门所有员工都高的员工信息（比最高的高）

SQL> select *

          from emp

          where sal >all (select sal from emp where deptno=30);

===》等价于

SQL> select *

          from emp

          where sal >(select max(sal) from emp where deptno=30)

-->结果：

all，和集合的所有值比较

--多行子查询中null，not in的问题,以及in的使用

--查询不是老板的员工信息

SQL> select *

          from emp

          where empno not in (select mgr from emp);

-->结果：未选定行（也就是没查到，原因是mgr里含有空值null)

--应该像下面这样用（把null值去掉）

SQL> select *

          from emp

          where empno not in (select mgr from emp where mgr is not null);

-->结果：

在多行查询中使用not in必须保证没有null

--查询是老板的员工信息

SQL> select *

          from emp

          where empno in (select mgr from emp)

-->结果：

在多行查询中使用in