SQL 基礎--> 子查詢，sql基礎--查詢

SQL 基礎--> 子查詢，sql基礎--查詢
--=========================
--SQL 基礎--> 子查詢
--=========================
 
一、子查詢
子查詢就是位於SELECT、UPDATE、或DELETE語句中內部的查詢
    
二、子查詢的分類
單行子查詢
返回零行或一行
多行子查詢
返回一行或多行
多列子查詢
返回多列
相互關聯的子查詢
引用外部SQL語句中的一列或多列
嵌套子查詢
位於其它子查詢中的查詢
 
三、子查詢文法

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
where empno = (select empno from emp where mgr = 7902);

--使用分組函數的子查詢
SQL>
select ename, job, sal from emp where sal > (select avg(sal) from emp);

--在HAVING子句中使用子查詢
SQL>
select deptno, min(sal)
 from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);

--在FROM 子句中使用子查詢
SQL>
    select empno, ename from (select empno, ename from emp where deptno = 20);
   
--單行子查詢中的常見錯誤
--子查詢的結果返回多於一行
SQL>
select empno, ename
 from emp
where sal = (select sal from emp where deptno = 20);
 
(select sal 返回多行
ORA-01427: single-row subquery returns more than one row

--子查詢中不能包含ORDER BY子句
SQL>
        select empno, ename
  from emp
 where sal > (select avg(sal) from emp order by empno);
order by empno)
 
ORA-00907: missing right parenthesis
       
--子查詢內部沒有返回行，如下語句可以正確執行，但沒有資料返回
SQL>
select ename, job
 from emp
where empno = (select empno from emp where mgr = 8000);
 
返回：no rows selected

六、多行子查詢
返回多個行
使用多行比較子IN ,ANY ,ALL

--在多行子查詢中使用IN 操作符
SQL>
select empno, ename, job
 from emp
where sal in (select max(sal) from emp group by deptno);

--在多行子查詢中使用ANY 操作符
備忘：
{
比如集合(1,2,3,4)
>any表示只要大於其中任意一個就行
>all表示要比其中的任何一個都大
}
SQL>
select empno, ename, job
 from emp
where sal < any (select avg(sal) from emp group by deptno);

--在多行子查詢中使用ALL 操作符
SQL>
select empno, ename, job
 from emp
where sal > all (select avg(sal) from emp group by deptno);

七、相互關聯的子查詢
子查詢中使用了主查詢中的某些欄位，主查詢每掃描一行都要執行一次子查詢

--查詢工資高於同一部門的員工的平均工資的 部門號，姓名，工資
SQL>
select deptno, ename, sal
 from emp
outer where sal > (select avg(sal)
                     from emp
                    inner where inner.deptno = outer.deptno);

--查詢負責管理其它員工的員工記錄(使用exists)
註：EXISTS 是判斷是否存在,和in類似,但效率要比in高
SQL>
select empno, ename
 from emp
outer where exists
(select empno from emp inner where inner.mgr = outer.empno);

--查詢不管理其它員工的職員(not exists)
SQL>
select empno, ename
 from emp
outer where not exists
(select empno from emp inner where inner.mgr = outer.empno)

備忘：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
 from dept d
where not exists (select 1 from emp e where e.deptno = d.deptno);

--IN與空值
SQL>
SELECT *
 FROM emp e
WHERE e.empno NOT IN (SELECT 7369
                        FROM dual
                      UNION ALL
                      SELECT NULL FROM dual);
SQL>
SELECT * FROM emp e WHERE e.empno IN ('7369', NULL);

註：子查詢要包含在括弧內
子查詢一般放在比較條件的右側
除非進行TOP－N 分析，否則不要在子查詢中使用ORDER BY。

八、多列子查詢
1、成對比較

--查詢工資為部門最高的記錄
SQL>
select *
 from scott.emp
where (sal, job) in (select max(sal), job from scott.emp group by job);

--非成對比較,實現了與上述類似的功能
SQL>
select *
 from scott.emp
where sal in (select max(sal) from scott.emp group by job)
  and job in (select distinct job from scott.emp);

九、嵌套子查詢
即位於子查詢內部的子查詢，嵌套層數最多可達層。然而應盡量避免使用嵌套子查詢，使用表串連的查詢效能會更高

SQL>
select deptno, Num_emp
 from (select deptno, count(empno) as Num_emp from emp group by deptno) d
where Num_emp > 3;

注意：子查詢對空值的處理除了count(*)外，都會忽略掉空值