SQL 子查詢到底難在哪？一篇文章講透單行子查詢，附實(shí)戰(zhàn)案例

很多人學(xué) SQL 時，最開始覺得 SELECT、WHERE、ORDER BY 都挺順手，結(jié)果一碰到子查詢，馬上開始犯迷糊。

其實(shí)子查詢一點(diǎn)都不神秘，你可以把它理解成：

先算出一個結(jié)果，再拿這個結(jié)果去篩數(shù)據(jù)。

這篇文章，我們就用最常見的員工表 emp 來講清楚：

• 什么是子查詢
• 為什么子查詢比“分兩步查”更實(shí)用
• 什么是不相關(guān)子查詢
• 什么是單行子查詢
• 3 個高頻案例實(shí)戰(zhàn)
• 常見報錯與避坑建議

如果你正好在學(xué)數(shù)據(jù)庫，這篇建議收藏。

一、什么是子查詢？

先看一個非常典型的需求：

查詢工資高于 CLARK 的所有員工信息。

很多初學(xué)者會怎么做？

寫法 1：分兩步查

先查出 CLARK 的工資：

SELECT salFROM empWHERE ename = 'CLARK';

運(yùn)行結(jié)果：

2450

再查工資大于 2450 的員工：

SELECT *FROM empWHERE sal > 2450;

運(yùn)行結(jié)果示意：

EMPNO ENAME  JOB       MGR   HIREDATE    SAL   COMM  DEPTNO7839  KING   PRESIDENT NULL  1981-11-17  5000  NULL  107566  JONES  MANAGER   7839  1981-04-02  2975  NULL  207788  SCOTT  ANALYST   7566  1987-04-19  3000  NULL  207902  FORD   ANALYST   7566  1981-12-03  3000  NULL  207698  BLAKE  MANAGER   7839  1981-05-01  2850  NULL  30

這種方式能不能查出來？當(dāng)然能。

但問題也很明顯：

1. 需要執(zhí)行兩條 SQL；
2. 第一步的結(jié)果要人工接到第二步里；
3. 如果 CLARK 的工資變了，第二條 SQL 里的數(shù)值也得跟著改。

這就是很多人寫 SQL 時效率不高的根源。

二、子查詢?yōu)槭裁锤糜茫?/p>

還是剛才那個需求，我們直接合并成一條 SQL：

SELECT *FROM empWHERE sal > (SELECT salFROM empWHERE ename = 'CLARK');

這條 SQL 的意思很直接：

• 先執(zhí)行括號里的查詢，拿到 CLARK 的工資；
• 再執(zhí)行外層查詢，找出工資比它高的人。

也就是說：

一條 SQL 語句中嵌套了另一條 SELECT 語句，這種結(jié)構(gòu)就叫子查詢。

子查詢最重要的一個原則：

先執(zhí)行子查詢，再執(zhí)行外層查詢。

比如下面這句：

SELECT *FROM empWHERE sal > (SELECT salFROM empWHERE ename = 'CLARK');

實(shí)際執(zhí)行邏輯相當(dāng)于：

第一步：執(zhí)行子查詢

SELECT salFROM empWHERE ename = 'CLARK';

得到：

2450

SELECT *FROM empWHERE sal > 2450;

所以你會發(fā)現(xiàn)，子查詢本質(zhì)上并不玄學(xué)，它只是把原來兩步操作寫進(jìn)了一條 SQL。

四、什么是不相關(guān)子查詢？

很多教程一上來就講“相關(guān)子查詢、不相關(guān)子查詢”，把人繞暈。

其實(shí)判斷方法很簡單：

如果子查詢單獨(dú)拿出來能獨(dú)立運(yùn)行，那它通常就是不相關(guān)子查詢。

比如：

SELECT salFROM empWHERE ename = 'CLARK';

這條語句本身就能獨(dú)立執(zhí)行，也能得到正常結(jié)果。

所以放到外層查詢里后，它屬于：

不相關(guān)子查詢

也就是說，子查詢不依賴外層查詢的數(shù)據(jù)。

五、不相關(guān)子查詢還可以分兩類1. 單行子查詢

子查詢返回一行結(jié)果。

比如：

SELECT salFROM empWHERE ename = 'CLARK';

結(jié)果只有一行：2450

或者：

SELECT AVG(sal)FROM emp;

平均工資也只會返回一個值。

這類就是單行子查詢。

2. 多行子查詢

子查詢返回多行結(jié)果。

比如：

SELECT salFROM empWHERE deptno = 10;

如果 10 號部門有多個人，那就會返回多條記錄。

這類就是多行子查詢。

六、單行子查詢到底怎么寫？3 個案例直接吃透

下面直接上實(shí)戰(zhàn)。

案例 1：查詢工資高于平均工資的員工姓名和工資

這個題其實(shí)是在考你兩件事：

1. 員工姓名和工資從哪張表查；
2. 平均工資怎么作為條件嵌進(jìn)去。

SELECT ename, salFROM empWHERE sal > (SELECT AVG(sal)FROM emp);

先執(zhí)行：

SELECT AVG(sal) FROM emp;

假設(shè)運(yùn)行結(jié)果：

2073.21

再執(zhí)行外層：

SELECT ename, salFROM empWHERE sal > 2073.21;

ENAME  SALJONES  2975BLAKE  2850CLARK  2450SCOTT  3000KING   5000FORD   3000

• AVG(sal) 返回的是一個值，所以這是單行子查詢
• 外層用 > 沒問題，因?yàn)橛疫呏挥幸粋€結(jié)果

這個題比前一個多了一個條件：

• 同部門
• 工資更低

所以可以寫兩個單行子查詢。

SQL 寫法

SELECT ename, salFROM empWHERE deptno = (SELECT deptnoFROM empWHERE ename = 'CLARK'AND sal < (SELECT salFROM empWHERE ename = 'CLARK');

SELECT deptnoFROM empWHERE ename = 'CLARK';

結(jié)果：

10

SELECT salFROM empWHERE ename = 'CLARK';

結(jié)果：

2450

SELECT ename, salFROM empWHERE deptno = 10AND sal < 2450;

ENAME  SALMILLER 1300

遇到這種題，建議你先把自然語言拆成條件：

與 CLARK 同部門，并且比 CLARK 工資低

直接拆成：

• deptno = CLARK 的 deptno
• sal < CLARK 的 sal

然后每個“CLARK 的某個字段”都可以交給子查詢?nèi)ゲ椤?/p>

這個思路一旦養(yǎng)成，寫 SQL 會快很多。

案例 3：查詢與 SCOTT 職務(wù)相同、且入職時間早于 SCOTT 的員工信息

這題查的是：

• 職務(wù)相同
• 入職時間更早

SELECT *FROM empWHERE job = (SELECT jobFROM empWHERE ename = 'SCOTT'AND hiredate < (SELECT hiredateFROM empWHERE ename = 'SCOTT');

SELECT jobFROM empWHERE ename = 'SCOTT';

結(jié)果：

ANALYST

SELECT hiredateFROM empWHERE ename = 'SCOTT';

結(jié)果：

1987-04-19

SELECT *FROM empWHERE job = 'ANALYST'AND hiredate < '1987-04-19';

EMPNO ENAME JOB      MGR  HIREDATE    SAL  COMM DEPTNO7902  FORD  ANALYST  7566 1981-12-03  3000 NULL 20

子查詢本身不難，真正難的是結(jié)果集和運(yùn)算符要匹配。

1. 單行子查詢要配單值運(yùn)算符

像這些：

它們默認(rèn)右邊應(yīng)該是一個值。

比如：

SELECT ename, salFROM empWHERE sal > (SELECT AVG(sal)FROM emp);

這是對的，因?yàn)?AVG(sal) 只返回一個值。

2. 如果子查詢返回多行，就不能亂用 =

比如這句就可能報錯：

SELECT *FROM empWHERE deptno = (SELECT deptnoFROM empWHERE job = 'MANAGER');

因?yàn)榻?jīng)理可能不止一個，對應(yīng)多個部門號。

這時候數(shù)據(jù)庫通常會報類似錯誤：

ORA-01427: single-row subquery returns more than one row

意思就是：

單行子查詢居然返回了多行

這種情況就要考慮改用：

• IN
• ANY
• ALL

不過這已經(jīng)屬于多行子查詢的范圍了。

八、子查詢和硬編碼相比，優(yōu)勢到底在哪？

很多人一開始不理解，覺得直接寫死數(shù)字不是更快嗎？

比如：

SELECT *FROM empWHERE sal > 2450;

確實(shí)短，但問題在于它不靈活。

子查詢的優(yōu)勢主要有 3 點(diǎn)：1. 動態(tài)獲取條件值

不需要手動查完再填寫。

2. 降低維護(hù)成本

源數(shù)據(jù)變了，SQL 不用跟著改。

3. 邏輯更完整

整條 SQL 自己就能表達(dá)完整業(yè)務(wù)意圖，別人接手也更容易看懂。

九、寫子查詢時的 4 個實(shí)戰(zhàn)建議建議 1：先讀懂題，再寫 SQL

很多 SQL 寫不出來，不是語法不會，而是題意沒拆明白。

比如題目里說：

• 同部門
• 同職務(wù)
• 高于平均工資
• 早于某人入職時間

這些其實(shí)都是條件關(guān)鍵詞。

把條件一句句拆開，SQL 就自然出來了。

建議 2：先單獨(dú)運(yùn)行子查詢

不要一上來就整條 SQL 一把梭。

先把子查詢跑通，確認(rèn)它到底返回什么，再往外拼。

比如：

SELECT sal FROM emp WHERE ename = 'CLARK';

你先確認(rèn)結(jié)果確實(shí)只有一行，再去寫：

WHERE sal > (子查詢)

這樣最穩(wěn)。

建議 3：字段類型要對應(yīng)

例如：

• sal 對應(yīng)工資，數(shù)值類型
• hiredate 對應(yīng)日期類型
• job 對應(yīng)字符串類型

不要拿日期字段去和工資字段比較，這種低級錯誤非常常見。

建議 4：復(fù)雜 SQL 適當(dāng)換行

SQL 不是越擠越厲害，清晰最重要。

比如：

SELECT *FROM empWHERE job = (SELECT jobFROM empWHERE ename = 'SCOTT'AND hiredate < (SELECT hiredateFROM empWHERE ename = 'SCOTT');

這種排版明顯比一行糊到底更適合排查問題。

十、補(bǔ)一個完整測試腳本，拿去就能練

如果你想自己在數(shù)據(jù)庫里練習(xí)，可以參考下面這組語句。

1. 查詢 CLARK 工資

SELECT salFROM empWHERE ename = 'CLARK';

運(yùn)行結(jié)果：

2450

SELECT *FROM empWHERE sal > (SELECT salFROM empWHERE ename = 'CLARK');

運(yùn)行結(jié)果示意：

KINGJONESSCOTTFORDBLAKE

SELECT ename, salFROM empWHERE sal > (SELECT AVG(sal)FROM emp);

運(yùn)行結(jié)果示意：

JONES 2975BLAKE 2850CLARK 2450SCOTT 3000KING  5000FORD  3000

SELECT ename, salFROM empWHERE deptno = (SELECT deptnoFROM empWHERE ename = 'CLARK'AND sal < (SELECT salFROM empWHERE ename = 'CLARK');

運(yùn)行結(jié)果示意：

MILLER 1300

SELECT *FROM empWHERE job = (SELECT jobFROM empWHERE ename = 'SCOTT'AND hiredate < (SELECT hiredateFROM empWHERE ename = 'SCOTT');

運(yùn)行結(jié)果示意：

FORD ANALYST 1981-12-03

如果你看到一道子查詢題，總是沒思路，就按這個套路來：

第一步：看外層要查什么

比如查姓名、工資、員工全部信息。

第二步：看條件依賴誰

比如“比 CLARK 高”“和 SCOTT 一樣”“高于平均工資”。

第三步：把條件里的“參照值”交給子查詢

比如：

• CLARK 的工資
• CLARK 的部門
• SCOTT 的職務(wù)
• 全公司的平均工資

你會發(fā)現(xiàn)，所謂子查詢，本質(zhì)上就是：

把條件中的動態(tài)值先查出來，再參與外層過濾。

掌握這一點(diǎn)，單行子查詢基本就不會卡了。