- 什么是子查詢?
- 子查詢是指嵌入在其他SQL語句中的SELECT語句,也稱之為嵌套查詢。
- 可以使用子查詢的位置:where、select列表、having、from。
a、在where子句中使用子查詢
- 示例:查詢出和FORD是相同職位的員工:select * from emp where job = (select job from emp where ename='FORD');
SQL> select * from emp where job = (select job from emp where ename='FORD');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
7951 EASON ANALYST 7566 01-12月-17 3000
20
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7902 FORD ANALYST 7566 03-12月-81 3000
20
b、在select子句中使用子查詢
- 示例:查詢出每個部門的編號、名稱、位置和部門人數。示例:select deptno, dname, loc, (select count(empno) from emp where emp.deptno = dept.deptno) count from dept;
SQL> select deptno, dname, loc, (select count(empno) from emp where emp.deptno = dept.deptno) count from dept;
DEPTNO DNAME LOC COUNT
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 6
30 SALES CHICAGO 6
40 OPERATIONS BOSTON 0
c、在having子句中使用子查詢
- 舉例:查詢員工信息表,按照部門編號進行分組,要求顯示員工的部門編號、平均工資,查詢條件時平均工資大于30號部門的最高工資。
SQL> select deptno, avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno = 30);
DEPTNO AVG(SAL)
10 2916.66667
d、在from子句中使用子查詢
- 把子查詢的結果看成一張新的表。示例:查詢并顯示高于部門平均工資的雇員信息。
SQL> select ename, job, sal from emp, (select deptno, avg(sal) avgsal from emp group by deptno) dept where emp.deptno = dept.deptno and sal > avgsal;
ENAME JOB SAL
EASON ANALYST 3000
ALLEN SALESMAN 1600
JONES MANAGER 2975
BLAKE MANAGER 2850
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
已選擇7行。
2、Oracle的主查詢和子查詢
- 什么是主查詢和子查詢?
a、一個主查詢可以有多個子查詢
- 舉例:顯示職位和7521的職位相同并工資大于7934這個員工工資的員工信息。
SQL> select * from emp where job = (select job from emp where empno = 7521) and sal > (select sal from emp where empno = 7934);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
b、子查詢的執行順序
- 一般先執行子查詢,再執行主查詢,但相關子查詢例外。
- 舉例:查詢員工表中小于平均工資的員工信息。
SQL> select empno, ename, sal from emp where sal < (select avg(sal) from emp);
EMPNO ENAME SAL
7369 G_EASON 800
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7934 MILLER 1300
已選擇8行。
c、相關子查詢
- 當子查詢需要引用主查詢的表列時,Oracle會執行相關查詢。
- 相關子查詢是先執行主查詢,在執行子查詢。
- 示例:查詢工資高于部門平均工資的雇員名、工資和部門號:
SQL> select ename, sal, deptno from emp e where sal > (select avg(sal) from emp where deptno = e.deptno);
ENAME SAL DEPTNO
EASON 3000 20
ALLEN 1600 30
JONES 2975 20
BLAKE 2850 30
SCOTT 3000 20
KING 5000 10
FORD 3000 20
已選擇7行。
d、主查詢和子查詢可以不是同一張表
- 主查詢和子查詢可以查詢的不是同一張表。
- 示例:查詢部門名稱時ACCOUNTING的員工信息。
SQL> select * from emp where deptno = (select deptno from dept where dname='ACCOUNTING');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7839 KING PRESIDENT 17-11月-81 5000
10
7934 MILLER CLERK 7782 23-1月 -82 1300
10
3、Oracle的子查詢:單行子查詢
- 子查詢的類型:單行子查詢和多行子查詢。
- 單行子查詢:只返回一行數據的子查詢語句。
- 使用單行比較操作符:
- 示例1:顯示與JAMES同部門的所有其他的員工姓名、工資、部門號。
SQL> select ename, sal, deptno from emp where deptno = (select deptno from emp where ename='JAMES') AND ename <> 'JAMES';
ENAME SAL DEPTNO-
ALLEN 1600 30
WARD 1250 30
MARTIN 1250 30
BLAKE 2850 30
- 示例2:查詢大于等于公司平均工資的員工的姓名、職位、工資。
SQL> select ename, job, sal from emp where sal >= (select avg(sal) from emp);
ENAME JOB SAL
EASON ANALYST 3000
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
已選擇7行。
- 示例3:查詢部門名稱不是‘SAVE’的員工。
SQL> select * from emp where deptno <> (select deptno from dept where dname= 'SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
7951 EASON ANALYST 7566 01-12月-17 3000
20
......
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已選擇9行。
b、非法使用單行子查詢
- 示例:select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno);
SQL> select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno);
select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno)
第 1 行出現錯誤:
ORA-01427: 單行子查詢返回多個行
- select max(sal) from emp group by deptno;sql語句返回多行數據。
SQL> select max(sal) from emp group by deptno;
MAX(SAL)
2850
3000
5000
4、Oracle的子查詢:多行子查詢
- 多行子查詢是指返回多行數據的子查詢語句。
- 使用多行比較操作符:(使用多行子查詢時必須使用多行比較操作符。)
- 示例:查詢工作地點在NEW YORK和CHICAGO的部門所對應的員工信息。
SQL> select * from emp where deptno in (select deptno from dept where loc='NEW YORK' or loc = 'CHICAGO');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
7934 MILLER CLERK 7782 23-1月 -82 1300
10
......
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
已選擇9行。
b、在多行子查詢中使用ALL操作符
- 示例:查詢高于30號部門所有員工工資的員工名、工資和部門號。
SQL> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30);
ENAME SAL DEPTNO
JONES 2975 20
EASON 3000 20
FORD 3000 20
SCOTT 3000 20
KING 5000 10
c、在多行子查詢中使用ANY操作符
- 示例:查詢高于10號部門任意一個員工工資的員工名、工資和部門號。
SQL> select ename, sal, deptno from emp where sal > any (select sal from emp where deptno = 10);
ENAME SAL DEPTNO
KING 5000 10
EASON 3000 20
......
ALLEN 1600 30
TURNER 1500 30
已選擇9行。
5、Oracle的子查詢需要注意的問題
a、不可以在group by子句中使用子查詢。
- 示例:select avg(sal) from emp group by (select deptno from emp);
SQL> select avg(sal) from emp group by (select deptno from emp);
select avg(sal) from emp group by (select deptno from emp)
第 1 行出現錯誤:
ORA-22818: 這里不允許出現子查詢表達式
b、在TOP-N分析問題中,需對子查詢排序
- 示例:顯示員工信息表中工資最高的前五名員工。
SQL> select rownum, empno, ename, sal from (select * from emp order by sal desc) where rownum <= 5;
ROWNUM EMPNO ENAME SAL
1 7839 KING 5000
2 7951 EASON 3000
3 7902 FORD 3000
4 7788 SCOTT 3000
5 7566 JONES 2975
c、單行子查詢的空值問題
- 示例:select ename, job, from emp where job = (select job from emp wehre ename = 'Ruby');
SQL> select ename, job from emp where job = (select job from emp where ename = 'Ruby');
未選定行
- 如果子查詢返回到的是一個空值,那么主查詢將不會查詢到任何結果。
d、多行子查詢的空值問題
- 示例:select * from emp where empno not in (select mgr from emp);
SQL> select mgr from emp;
MGR
7566
null
......
7839
7566
7698
已選擇15行。
SQL> select * from emp where empno not in (select mgr from emp);
未選定行
- 多行子查詢中如果出現空值,則主查詢將不會查詢到任何結果。
SQL> select * from emp where empno not in (select mgr from emp where mgr is not null);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7951 EASON ANALYST 7566 01-12月-17 3000
20
......
7934 MILLER CLERK 7782 23-1月 -82 1300
10
7900 JAMES CLERK 7698 03-12月-81 950
30
已選擇9行。