oracle 子查询

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

SELECT * FROM EMP WHERE SAL > （SELECT SAL FROM EMP WHERE ENAME='SCOTT');

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17-11月-81           5000
        10

子查询需要注意的10个问题：

子查询语法中的小括号问题

SELECT *
  2  FROM EMP
  3  WHERE SAL > SELECT SAL
  4          FROM EMP
  5*                      WHERE ENAME='SCOTT'
SQL> /
WHERE SAL > SELECT SAL--子查询没有带括号
            *
第 3 行出现错误:
ORA-00936: 缺失表达式

子查询的书写风格问题

该换行的换行，该缩进的缩进。

可以使用子查询的位置：WHERE,SELECT,HAVING,FROM

SELECT EMPNO,ENAME,SAL,(SELECT JOB FROM EMP WHERE EMPNO=7839) 第四列
  2  FROM EMP;--SELECT 语句只能使用单行子查询，子查询返回单个结果为单行子查询，多个结果为多行子查询

SELECT DEPTNO,AVG(SAL)
  2  FROM EMP
  3  GROUP BY DEPTNO
  4  HAVING AVG(SAL) > (SELECT MAX(SAL)
  5                     FROM EMP
  6                     WHERE DEPTNO=30);

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667

SELECT * FROM (SELECT EMPNO,ENAME,SAL FROM EMP);--ORACLE中很多问题都是通过FROM子查询解决

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

不可以使用子查询的位置

GROUP BY

SELECT AVG(SAL)
  2  FROM EMP
  3  GROUP BY (SELECT DEPTNO
  4             FROM EMP);
GROUP BY (SELECT DEPTNO
          *
第 3 行出现错误:
ORA-22818: 这里不允许出现子查询表达式

FROM后面的子查询（*）

查询员工信息：员工号，姓名，月薪

SELECT * FROM (SELECT EMPNO,ENAME,SAL FROM EMP);

查询员工信息：员工号，姓名，月薪，年薪

SELECT * FROM (SELECT EMPNO,ENAME,SAL，SAL*12 ANNSAL FROM EMP);

     EMPNO ENAME             SAL     ANNSAL
---------- ---------- ---------- ----------
      7369 SMITH             800       9600
      7499 ALLEN            1600      19200
      7521 WARD             1250      15000
      7566 JONES            2975      35700
      7654 MARTIN           1250      15000
      7698 BLAKE            2850      34200
      7782 CLARK            2450      29400
      7788 SCOTT            3000      36000
      7839 KING             5000      60000
      7844 TURNER           1500      18000
      7876 ADAMS            1100      13200

     EMPNO ENAME             SAL     ANNSAL
---------- ---------- ---------- ----------
      7900 JAMES             950      11400
      7902 FORD             3000      36000
      7934 MILLER           1300      15600

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

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

SELECT *
  2  FROM EMP
  3  WHERE DEPTNO=(SELECT DEPTNO
  4               FROM DEPT
  5                     WHERE DNAME='SALES');

SELECT E.*
FROM EMP E，DEPT D
WHERE  E.DEPTNO=D.DEPTNO AND D.DNAME='SALES';

理论上，多表查询比较好，实际情况子查询可能比多表查询好，需要考虑多表查询产生的笛卡尔集，具体用哪种根据实际情况而定。

一般不在子查询中使用排序；但在TOP N分析问题中，必须对子查询进行排序。

找到员工表中工资最高的前三名

SELECT ROWNUM,EMPNO,ENAME,SAL
FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE  ROWNUM<=3;--ROWNUM 行号 伪列,行号永远按默认的顺序生成，行号只能使用<,<=;不能使用>,>=.

主查询和子查询的执行顺序

一般先执行主查询再执行子查询；但相关查询例外。

找到员工表中薪水大于本部门平均薪水的员工

 SELECT EMPNO,ENAME,SAL,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO) AVGSAL
  2  FROM EMP E
  3  WHERE SAL > (SELECT AVG(SAL)
  4  FROM EMP
  5  WHERE DEPTNO=E.DEPTNO);

     EMPNO ENAME             SAL     AVGSAL
---------- ---------- ---------- ----------
      7499 ALLEN            1600 1566.66667
      7566 JONES            2975       2175
      7698 BLAKE            2850 1566.66667
      7788 SCOTT            3000       2175
      7839 KING             5000 2916.66667
      7902 FORD             3000       2175

单行子查询和多行子查询

单行子查询只能使用单行操作符，多行子查询只能使用多行操作符。

查询员工信息：要求职位与7566员工一样，同时薪水大于7782员工的薪水

 SELECT *
  2  FROM EMP
  3  WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=7566) AND
  4    SAL > (SELECT SAL FROM EMP WHERE EMPNO=7782);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850

查询工资最低的员工信息

SELECT *
FROM EMP 
WHERE SAL = (SELECT MIN(SAL) FROM EMP);

查询最低工资大于20号部门的最低工资的部门号和最低工资

SELECT DEPTNO,MIN(SAL)
  2  FROM EMP
  3  GROUP BY DEPTNO
  4  HAVING MIN(SAL) > (SELECT MIN(SAL)
  5  FROM EMP
  6  WHERE DEPTNO=20);

    DEPTNO   MIN(SAL)
---------- ----------
        30        950
        10       1300

多行操作符IN

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

SELECT *
  2  FROM EMP
  3  WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES' OR DNAME='ACCOUNTING');

SELECT E.*
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND (D.DNAME='SALES' OR D.DNAME='ACCOUNTING');

多行操作符ANY

查询工资比30号部门任意一个员工工资高的员工信息

SELECT *
FROM EMP 
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30);

SELECT *
FROM EMP 
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30);

多行操作符ALL

查询工资比30号部门所有员工工资高的员工信息

SELECT *
FROM EMP 
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);
SELECT *
FROM EMP 
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

子查询中的NULL值问题

单行子查询

SELECT ENAME,JOB
  2  FROM EMP
  3  WHERE JOB = (SELECT JOB
  4  FROM EMP
  5  WHERE ENAME = 'TOM');

未选定行--子查询不返回任何行。

多行子查询

查询不是老板的员工(只要多行子查询有空值，不要用NOT IN,可以用IN）

SELECT *
FROM EMP 
WHERE EMPNO NOT IN (SELECT MGR 
					FROM EMP
					WHERE MGR IS NOT NULL);