子查詢概述

子查詢

SELECT select_listFROM tableWHERE exPR Operator (SELECT select_list FROM table)括號內的查詢叫做子查詢(內部查詢)，先于主查詢執行子查詢的結果被主查詢(外部查詢)使用operator包括：=、>、>=、<、<=、<>、IN、ANY、ALL子查詢可以嵌于WHERE子句、HAVING子句、FROM子句中
子查詢的類型
根據子查詢返回的行和列的數量，子查詢分為單行子查詢、多行子查詢和多列子查詢
子查詢使用指導
子查詢要用括號括起來將子查詢放在比較運算符的右邊對于單行子查詢要使用單行運算符對于多行子查詢要使用多行運算符
SELECT enameFROM empWHERE sal>(SELECT sal FROM emp WHERE ename='JONES')
單行子查詢
只返回一行一列的子查詢稱為單行子查詢單行運算符：
運算符
含義
=
等于
>
大于
>=
大于等于
<
小于
<=
小于等于
<>
不等于
多行子查詢
多行子查詢
可能返回多條記錄的子查詢稱為多行子查詢多行運算符：IN、ANY、ALL
多行操作符：IN
判斷是否與子查詢的任意一個返回值相同IN的使用
-- 查詢是經理的員工姓名、工資SELECT ename,salFROM empWHERE empno IN (SELECT mgr FROM emp)
多行操作符：ANY
表示和子查詢的任意一行結果進行比較，有一個滿足條件即可< ANY：表示小于子查詢結果集中的任意一個，即小于最大值就可以> ANY：表示大于子查詢結果集中的任意一個，即大于最小值就可以= ANY：表示等于子查詢結果中的任意一個，即等于誰都可以，相當于INANY的使用
-- 查詢是經理的員工姓名、工資SELECT ename,salFROM empWHERE empno = ANY (SELECT mgr FROM emp)						-- 查詢部門編號不為10，且工資比10部門任意一名員工工資低的員工編號、姓名、職位、工資SELECT empno,ename,job,salFROM empWHERE sal < ANY (SELECT sal                 FROM emp                 WHERE deptno=10)AND deptno <> 10
多行操作符：ALL
表示和子查詢的所有行結果進行比較，每一行必須都滿足條件< ALL：表示小于子查詢結果集中的所有行，即小于最小值> ALL：表示大于子查詢結果集中的所有行，即大于最大值= ALL：表示等于子查詢結果中的所有行，即等于所有值，通常無意義ALL的使用
-- 查詢部門編號不為10，且工資比10部門所有員工工資低的員工編號、姓名、職位、工資SELECT empno,ename,job,salFROM empWHERE sal < ALL (SELECT sal                 FROM emp                 WHERE deptno=10)AND deptno <> 10
多列子查詢
可以在一個條件表達式內同時和子查詢的多個列進行比較的子查詢稱為多列子查詢多列子查詢通常使用IN操作符完成
-- 查詢出和1981年入職的任意一個員工的部門和職位完全相同的員工姓名、部門、職位、入職日期，不包括1981年入職員工SELECT ename,deptno,job,hiredateFROM empWHERE (deptno,job) IN (SELECT deptno,job                       FROM emp                       WHERE TO_CHAR(hiredate,'YYYY')='1981')AND TO_CHAR(hiredate,'YYYY')<>'1981'
子查詢中空值問題
子查詢返回的結果中含有空值會導致主查詢沒有記錄返回所有的條件和空值比較的結果都是空值無論什么時候只要空值有可能成為子查詢結果集中的一部分，就不能使用NOT IN運算符
在FROM子句中使用子查詢
-- 查詢比自己部門平均工資高的員工姓名、工資、部門編號、部門平均工資SELECT ename,sal,e.deptno,avgsalFROM emp e,(SELECT deptno,AVG(sal) avgsal            FROM emp            GROUP BY deptno) sWHERE e.deptno = s.deptnoAND sal>avgsal
分頁查詢
ROWNUM及其特性
ROWNUM是一個偽列，偽列是使用上類似于表中的列，而實際并沒有存儲在表中的特殊列ROWNUM的功能是在每次查詢時，返回結果集的順序號，這個順序號是在記錄輸出時逐行產生的“SELECT * FROM emp WHERE ROWNNUM>2;”為何查不到任何記錄？因為ROWNUM是在記錄輸出時才逐行產生的，且總是從1開始，所以輸出的第一條記錄不滿足“ROWNUM>2”的條件，被過濾掉；第二條的ROWNUM變成了1，又不滿足“ROWNUM>2”的條件，又被過濾掉；以此類推，永遠沒有滿足條件的記錄，所以返回為空即查不到任何記錄對于ROWNUM只能執行<、<=運算，不能執行>、>=或一個區間運算BETWEEN...AND等因為ROWNUM在記錄輸出時生成，而ORDER BY子句在最后執行，所以ROWNUM和ORDER BY子句一起使用時，需要注意ROWNUM實際是已經被排了序的ROWNUM
TOP-N查詢
TOP-N查詢主要是實現表中按照某個列排序，輸出最大或最小的N條記錄功能TOP-N分析語法：
SELECT [列名],ROWNUMFROM  (SELECT [列名]FROM 表名ORDER BY TOP-N操作的列 ASC|DESC)WHERE ROWNUM<=NASC：查詢最小的N條記錄DESC：查詢最大的N條記錄
分頁查詢
當未指定需要按照某列排序語法：
SELECT b.*FROM (SELECT ROWNUM rn,[列名1,列名2,...列名n]      FROM 表名,[表名2,...表名n]      WHERE [條件表達式 AND ]ROWNUM <= 目標頁碼*每頁記錄數) bWHERE rn > (目標頁碼-1)*每頁記錄數當未指定需要按照某列排序語法：
SELECT *FROM (SELECT ROWNUM rn,b.*      FROM (SELECT 列名1[,列名2,...列名n]            FROM 表名1[,表名2,...表名n]            [WHERE 子句]            ORDER BY 要排序的列 ASC|DESC) b      WHERE ROWNUM <= 目標頁碼*每頁記錄數)WHERE rn > (目標頁碼-1)*每頁記錄數