1. 子查詢的概念

1.1 子查詢的概念和理解

某些查詢邏輯中胎源，需要引入另一個查詢做為條件或者數(shù)據(jù)來源進行輔助赖淤。
比如：查詢與‘TOM’在同一部門工作的其他員工

select * from emp where deptno = TOM的deptno and ename <> 'TOM';

TOM的deptno

select deptno from emp where ename = 'TOM';

將兩個獨立的查詢合并起來

select * from emp 
where deptno = (select deptno from emp where ename = 'TOM')
and ename <> 'TOM';

整個外部這部分SQL語句被稱為外部查詢（主查詢）培慌，括號內查詢TOM部門號的這個SQL語句被稱為子查詢

image

1.2 子查詢使用位置

子查詢可以使用在很多子句中

• from子句中，from子句中的子查詢可以理解為是一張臨時的“表”（視圖）
• where子句中
• having子句中
  通常主要使用在where和from中

2. 簡單子查詢（單行子查詢）

單行子查詢的查詢結果是一行一列的數(shù)據(jù)
可以使用常規(guī)的> ,<, >=, <=, =, !=進行子查詢數(shù)據(jù)的比較
可以將子查詢理解為就是一個簡單的數(shù)據(jù)
示例1：查詢比TOM月薪高的員工信息

select * from emp where sal > (select sal from emp where ename = 'TOM')

image

示例2：查詢與LEE是同一職位同一部門的其他員工信息

select *
from emp
where job = (select job from emp where ename = 'LEE') 
and deptno = (select deptno from emp where ename = 'LEE')
and ename <> 'LEE'

image

子查詢中可以嵌套其他的子查詢榨为，層數(shù)限制在32層內
示例3：查詢月薪高于AMY所在部門平均月薪的員工信息

select *
from emp
where sal > (select AVG(sal) from emp 
         where deptno = (select deptno from emp where ename = 'AMY'))

image

3. 多行子查詢

3.1 多行子查詢

多行子查詢返回的是多行一列的數(shù)據(jù)
當子查詢返回多行記錄時锅睛，使用=這類的比較運算符無法執(zhí)行

image

當有多條記錄返回時，使用IN來進行比較义辕，相當于等于子查詢中某個值即可
示例4： 查詢是經(jīng)理的員工信息（=）

select *
from emp
where empno in (select distinct mgr from emp where mgr is not null)

image

示例5： 查詢不是經(jīng)理的員工信息（!=）

select *
from emp
where empno not in (select distinct mgr from emp where mgr is not null)

image

not in中的數(shù)據(jù)不能有null值虾标，如果有null值，相當于執(zhí)行了=null操作，不能篩選出任何數(shù)據(jù)

3.2 ANY（SOME）與ALL

主要使用在多行子查詢中進行>或<與操作時
ANY（SOME）： 任何一個
ALL： 所有

ANY 比最小的大
ALL 比最大的大
< ANY 比最大的小
< ALL 比最小的小

image

示例6：查詢比10部門所有人月薪都要高的員工信息

select *
from emp
where sal > ALL(select sal from emp where deptno = 10)

也可以使用

select *
from emp
where sal > (select MAX(sal) from emp where deptno = 10)

image

使用ANY和ALL執(zhí)行效率要高于分組函數(shù)

3.3 from子句中的子查詢

將子查詢運用在from中璧函，相當于一張“表”
必須為作為“表”的子查詢起“表別名”（示例7中的t）
示例7：查詢部門編號傀蚌，部門名稱，部門loc蘸吓，部門人數(shù)

select d.deptno,d.dname,d.loc,IFNULL(t.empnum,0)
from dept d left join
    (select deptno, COUNT(empno) as empnum
    from emp
    where deptno is not null
    group by deptno) t
     on(d.deptno = t.deptno) 

image

在MySQL中,update語句分組函數(shù)的結果不能作為子查詢的返回結果

update emp set sal = sal + 200
where sal < (select avg(sal) from emp);

MySQL中認為更新和子查詢不能同時進行善炫。
解決辦法：將子查詢再次進行嵌套，制作成From中的子查詢

update emp set sal = sal + 200
where sal < (select asal from(select avg(sal) from emp) t);

4. 相關子查詢（難點）

前3節(jié)的子查詢都屬于獨立子查詢库继，子查詢可以獨立執(zhí)行
相關子查詢：子查詢不可獨立執(zhí)行箩艺，必須依賴外部查詢

4.1 常規(guī)的相關子查詢

示例8：查詢比自己所在部門平均月薪高的員工信息
常規(guī)寫法

select * from emp e
      join (select deptno, AVG(sal) as asal
        from emp
        group by deptno) t
      on(e.deptno = t.deptno)
where e.sal > t.asal

相關子查詢寫法

select * from emp e1
where e1.sal > (select AVG(sal) from emp e2 where e2.deptno = e1.deptno)

image

相關子查詢的特點：外部查詢執(zhí)行一行數(shù)據(jù)，子查詢執(zhí)行一次

image

4.2 EXIST關鍵字

子查詢用于驗證外部查詢的一行記錄是否符合子查詢的條件
示例9： 查詢是經(jīng)理的員工的員工信息

select *
from emp e1
where exists (select empno from emp e2 where e2.mgr = e1.empno)

image

image

如果想表述不存在的邏輯宪萄，使用NOT EXISTS