/*
一、子查詢
子查詢就是位于SELECT、UPDATE、或DELETE語句中内部的查詢
二、子查詢的分類
單行子查詢
傳回零行或一行
多行子查詢
傳回一行或多行
多列子查詢
傳回多列
相關子查詢
引用外部SQL語句中的一列或多列
嵌套子查詢
位于其它子查詢中的查詢
三、子查詢文法 */
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
/*
子查詢(内部查詢)在執行主查詢之前執行一次
然後主查詢(外部查詢)會使用該子查詢的結果
四、子查詢的規則
将子查詢括在括号中
将子查詢放置在比較條件的右側
隻有在執行排序Top-N分析時，子查詢中才需要使用ORDER BY 子句
單行運算符用于單行子查詢，多行運算符用于多行子查詢
五、單行子查詢
僅傳回一行
使用單行的表較運算符：= ,>, >= ,< , <= ,<>
在WHERE 子句中使用子查詢 */
SQL> select ename,job from emp
2 where empno = (
3 select empno from emp
4 where mgr = 7902 );
ENAME JOB
---------- ---------
SMITH CLERK
--使用分組函數的子查詢
SQL> select ename,job,sal
2 from emp
3 where sal >
4 (select avg(sal) from emp);
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
--在HAVING子句中使用子查詢
SQL> select deptno,min(sal)
3 group by deptno
4 having min(sal) >
5 (select min(sal)
6 from emp
7 where deptno = 20);
DEPTNO MIN(SAL)
---------- ----------
30 950
10 1300
--在FROM 子句中使用子查詢
SQL> select empno,ename
2 from
3 (select empno,ename
4 from emp
5 where deptno = 20);
EMPNO ENAME
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
--單行子查詢中的常見錯誤
--子查詢的結果傳回多于一行
SQL> select empno,ename
2 from emp
3 where sal =
4 (select sal
5 from emp
6 where deptno = 20);
(select sal
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row
--子查詢中不能包含ORDER BY子句
3 where sal >
4 (select avg(sal)
6 order by empno);
order by empno)
ERROR at line 6:
ORA-00907: missing right parenthesis
--子查詢内部沒有傳回行，如下語句可以正确執行，但沒有資料傳回
SQL> select ename,job
3 where empno =
4 (select empno
6 where mgr = 8000);
no rows selected
/*
六、多行子查詢
傳回多個行
使用多行比較運算符IN ,ANY ,ALL
在多行子查詢中使用IN 操作符 */
SQL> select empno,ename,job
3 where sal in
4 (select max(sal)
5 from emp
6 group by deptno);
EMPNO ENAME JOB
---------- ---------- ---------
7698 BLAKE MANAGER
7902 FORD ANALYST
7788 SCOTT ANALYST
7839 KING PRESIDENT
--在多行子查詢中使用ANY 操作符
3 where sal < any
4 (select avg(sal)
7369 SMITH CLERK
7900 JAMES CLERK
7876 ADAMS CLERK
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7934 MILLER CLERK
7844 TURNER SALESMAN
7499 ALLEN SALESMAN
7782 CLARK MANAGER
--在多行子查詢中使用ALL 操作符
3 where sal > all
6* group by deptno)
7566 JONES MANAGER
/*
七、相關子查詢
子查詢中使用了主查詢中的某些字段，主查詢每掃描一行都要執行一次子查詢 */
--查詢工資高于同一部門的員工的部門号，姓名，工資
SQL> select deptno,ename,sal
2 from emp outer
5 from emp inner
6 where inner.deptno = outer.deptno);
DEPTNO ENAME SAL
---------- ---------- ----------
30 ALLEN 1600
20 JONES 2975
30 BLAKE 2850
20 SCOTT 3000
10 KING 5000
20 FORD 3000
--查詢負責管理其它員工的員工記錄(使用exists)
3 where exists
4 (select empno
5 from emp inner
6 where inner.mgr = outer.empno);
7698 BLAKE
7782 CLARK
7839 KING
--查詢不管理其它員工的職員(not exists)
SQL> l3
3* where exists
SQL> c /where/where not
3* where not exists
SQL> l
1 select empno,ename
3 where not exists
6* where inner.mgr = outer.empno)
SQL> /
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7900 JAMES
7934 MILLER
EXISTS 和NOT EXISTS 與IN 和NOT IN 的比較
EXISTS與IN的不同：
EXISTS隻檢查行的存在性,IN 要檢查實際值的存在性(一般情況下EXISTS的性能高于IN)
NOT EXISTS 和NOT IN
當值清單中包含空值的情況下,NOT EXISTS 則傳回true,而NOT IN 則傳回false.
--看下面的查詢，查詢部門号不在emp表中出現的部門名稱及位置
SQL> select deptno,dname,loc
2 from dept d
4 (select 1
5 from emp e
6* where e.deptno = d.deptno)
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
--IN與空值
SQL> SELECT *
2 FROM emp e
3 WHERE e.empno NOT IN (
4 SELECT 7369 FROM dual
5 UNION ALL
6 SELECT NULL FROM dual
7 )
8 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
3 WHERE e.empno IN ('7369',NULL)
4 ;
7369 SMITH CLERK 7902 1980-12-17 800.00 20
注：子查詢要包含在括号内
子查詢一般放在比較條件的右側
除非進行TOP－N 分析，否則不要在子查詢中使用ORDER
BY。 */
/*
八、多列子查詢
1、成對比較
查詢工資為部門最高的記錄 */
SQL> select * from scott.emp
2 where (sal,job) in
3 (select max(sal),job from scott.emp group by job);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
/*
2、非成對比較,實作了與上述類似的功能*/
2 where sal in (select max(sal) from scott.emp group by job)
3 and job in (select distinct job from scott.emp);
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
九、嵌套子查詢
即位于子查詢内部的子查詢，嵌套層數最多可達層。然而應盡量避免使用嵌套子查詢，使用表連接配接的查詢性能會更高*/
SQL> select deptno,Num_emp
2 from (select deptno,count(empno) as Num_emp from emp group by deptno) d
3 where Num_emp > 3;
DEPTNO NUM_EMP
30 6
20 5
/*
注意：子查詢對空值的處理
除了count(*)外，都會忽略掉空值 */
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)