SQL子查詢

在本教學中，您將了解SQL子查詢以及如何使用子查詢來形成靈活的SQL語句。

1. SQL子查詢基本

請考慮範例資料庫中的以下員工(employees)和部門(departments)表：

假設要查詢位置ID為1700的所有員工，可能會想出以下解決方案。

首先，找到位置ID為1700的所有部門：

SELECT 
    *
FROM
    departments
WHERE
    location_id = 1700;

執行上面查詢語句，得到以下結果：

+---------------+-----------------+-------------+
| department_id | department_name | location_id |
+---------------+-----------------+-------------+
|             1 | 管理            |        1700 |
|             3 | 採購            |        1700 |
|             9 | 行政人員        |        1700 |
|            10 | 財務            |        1700 |
|            11 | 會計            |        1700 |
+---------------+-----------------+-------------+
5 rows in set

其次，使用上一個查詢的部門ID列表，查詢屬於位置ID為1700的所有員工：

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    department_id IN (1 , 3, 8, 10, 11)
ORDER BY first_name , last_name;

執行上面範例程式碼，得到以下結果：

該解決方案有兩個問題。 首先，查詢departments表以檢查哪個部門屬於位置ID為1700。

由於資料量較小，可以輕鬆獲得部門列表。 但是，在具有大量資料的實際系統中，可能存在問題。

另一個問題是，只要想找到其它位置的員工，就必須修改查詢。
更好的解決方案是使用子查詢。 根據定義，子查詢是巢狀在另一個查詢中的查詢，例如：SELECT，INSERT，UPDATE或DELETE語句。 在本教學中，我們將重點介紹與SELECT語句一起使用的子查詢。

在此範例中，可以重寫上面的兩個查詢，如下所示：

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    department_id IN (SELECT 
            department_id
        FROM
            departments
        WHERE
            location_id = 1700)
ORDER BY first_name , last_name;

放在括號內的查詢稱為子查詢，它也稱為內部查詢或內部選擇。 包含子查詢的查詢稱為外部查詢或外部選擇。

要執行查詢，首先，資料庫系統必須執行子查詢並將括號之間的子查詢替換為其結果 - 位於位置ID為1700的多個部門ID - 然後執行外部查詢。

可以在許多地方使用子查詢，例如：

• 使用IN或NOT IN運算子
• 比較運算子中
• 使用EXISTS或NOT EXISTS運算子
• 使用ANY或ALL運算子
• 在FROM子句中
• 在SELECT子句中

2. SQL子查詢範例

下面我們舉一些使用子查詢來了解它們如何工作的例子。

2.1. 帶有IN或NOT IN運算子的SQL子查詢

在前面的範例中，已經了解了子查詢如何與IN運算子一起使用。 以下範例使用帶有NOT IN運算子的子查詢來查詢未找到位置ID為1700的所有員工：

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    department_id NOT IN (SELECT 
            department_id
        FROM
            departments
        WHERE
            location_id = 1700)
ORDER BY first_name , last_name;

執行上面查詢語句，得到以下結果：

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         103 | Alexander  | Lee       |
|         193 | Britney    | Zhao      |
|         104 | Bruce      | Wong      |
|         179 | Charles    | Yang      |
|         105 | David      | Liang     |
|         107 | Diana      | Chen      |
|         204 | Hermann    | Wu        |
|         126 | Irene      | Liu       |
|         177 | Jack       | Yang      |
|         145 | John       | Liu       |
|         176 | Jonathon   | Yang      |
|         146 | Karen      | Liu       |
|         178 | Kimberely  | Yang      |
|         120 | Matthew    | Han       |
|         121 | Max        | Han       |
|         201 | Michael    | Zhou      |
|         122 | Min        | Liu       |
|         202 | Pat        | Zhou      |
|         192 | Sarah      | Yang      |
|         123 | Shanta     | Liu       |
|         203 | Susan      | Zhou      |
|         106 | Valli      | Chen      |
+-------------+------------+-----------+
22 rows in set

2.2. 帶有比較運算子的SQL子查詢
以下語法說明了子查詢如何與比較運算子一起使用：

comparison_operator (subquery)

比較運算子是這些運算子之一：

• 等於(=)
• 大於(>)
• 小於(<)
• 大於或等於(>=)
• 小於等於(<=)
• 不相等(!=)或(<>)

以下範例查詢薪水最高的員工：

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary = (SELECT 
            MAX(salary)
        FROM
            employees)
ORDER BY first_name , last_name;

執行上面範例程式碼，得到以下結果：

ORDER BY first_name , last_name;
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         100 | Steven     | Lee       | 24000  |
+-------------+------------+-----------+--------+
1 row in set

在此範例中，子查詢返回所有員工的最高薪水，外部查詢查詢薪水等於最高員工的員工。

以下語句查詢所有薪水都高於的平均薪水的員工：

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            employees);

執行上面查詢語句，得到以下結果：

在此範例中，首先，子查詢返回所有員工的平均工資。 然後，外部查詢使用大於運算子來查詢工資大於平均值的所有員工。

2.3. 帶有EXISTS或NOT EXISTS運算子的SQL子查詢

EXISTS運算子檢查子查詢返回的行是否存在。 如果子查詢包含任何行，則返回true。 否則，它返回false。

EXISTS運算子的語法如下：

EXISTSE  (subquery )

NOT EXISTS運算子與EXISTS運算子相反。

NOT EXISTS (subquery)

以下範例查詢至少有一名員工的薪水大於10000的所有部門：

SELECT 
    department_name
FROM
    departments d
WHERE
    EXISTS( SELECT 
            1
        FROM
            employees e
        WHERE
            salary > 10000
                AND e.department_id = d.department_id)
ORDER BY department_name;

執行上面查詢語句，得到以下結果：

同樣，以下語句查詢所有沒有薪水大於10000的員工的部門：

SELECT 
    department_name
FROM
    departments d
WHERE
    NOT EXISTS( SELECT 
            1
        FROM
            employees e
        WHERE
            salary > 10000
                AND e.department_id = d.department_id)
ORDER BY department_name;

執行上面查詢語句，得到以下結果：

2.4. 帶有ALL運算子的SQL子查詢
子查詢與ALL運算子一起使用時的語法如下：

comparison_operator ALL (subquery)

如果x大於子查詢返回的每個值，則以下條件的計算結果為true。

x > ALL (subquery)

例如，假設子查詢返回三個值：1,2和3。 如果x大於3，則以下條件的計算結果為true。

x > ALL (1,2,3)

以下查詢使用GROUP BY子句和MIN()函式按部門查詢最低工資：

SELECT 
    MIN(salary)
FROM
    employees
GROUP BY department_id
ORDER BY MIN(salary) DESC;

執行上面查詢語句，得到以下結果：

以下範例查詢薪水大於每個部門最低薪水的所有員工：

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= ALL (SELECT 
            MIN(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY first_name , last_name;

執行上面查詢語句，得到以下結果：

2.5. 帶有ANY運算子的SQL子查詢
以下是帶有ANY運算子的子查詢的語法：

comparison_operator ANY (subquery)

例如，如果x大於子查詢返回的任何值，則以下條件的計算結果為true。 因此，如果x大於1，則條件x> SOME(1,2,3)的計算結果為true。

x > ANY (subquery)

請注意，SOME運算子是ANY運算子的同義詞，因此可以互換使用它們。

以下查詢查詢薪水大於或等於每個部門的最高薪水的所有員工。

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= SOME (SELECT 
            MAX(salary)
        FROM
            employees
        GROUP BY department_id);

執行上面查詢語句，得到以下結果：

+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         100 | Steven     | Lee       | 24000  |
|         101 | Neena      | Wong      | 17000  |
|         102 | Lex        | Liang     | 17000  |
|         103 | Alexander  | Lee       | 9000   |
|         104 | Bruce      | Wong      | 6000   |
|         105 | David      | Liang     | 4800   |
|         106 | Valli      | Chen      | 4800   |
|         108 | Nancy      | Chen      | 12000  |
... ... 
|         200 | Jennifer   | Zhao      | 4400   |
|         201 | Michael    | Zhou      | 13000  |
|         202 | Pat        | Zhou      | 6000   |
|         203 | Susan      | Zhou      | 6500   |
|         204 | Hermann    | Wu        | 10000  |
|         205 | Shelley    | Wu        | 12000  |
|         206 | William    | Wu        | 8300   |
+-------------+------------+-----------+--------+
31 rows in set

在此範例中，子查詢查詢每個部門中員工的最高薪水。 外部查詢檢視這些值並確定哪個員工的工資大於或等於按部門劃分的任何最高工資。

2.7. FROM子句中的SQL子查詢

可以在SELECT語句的FROM子句中使用子查詢，如下所示：

SELECT 
    *
FROM
    (subquery) AS table_name

在此語法中，表別名是必需的，因為FROM子句中的所有表都必須具有名稱。
請注意，FROM子句中指定的子查詢在MySQL中稱為派生表，在Oracle中稱為內聯檢視。

以下語句返回每個部門的平均工資：

SELECT 
    AVG(salary) average_salary
FROM
    employees
GROUP BY department_id;

執行上面查詢語句，得到以下結果：

可以將此查詢用作FROM子句中的子查詢，以計算部門平均工資的平均值，如下所示：

SELECT 
    ROUND(AVG(average_salary), 0)
FROM
    (SELECT 
        AVG(salary) average_salary
    FROM
        employees
    GROUP BY department_id) department_salary;
+-------------------------------+
| ROUND(AVG(average_salary), 0) |
+-------------------------------+
| 8536                          |
+-------------------------------+
1 row in set

2.8. SELECT子句中的SQL子查詢
可以在SELECT子句中使用表示式的任何位置使用子查詢。 以下範例查詢所有員工的工資，平均工資以及每個員工的工資與平均工資之間的差值。

SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    (SELECT 
            ROUND(AVG(salary), 0)
        FROM
            employees) average_salary,
    salary - (SELECT 
            ROUND(AVG(salary), 0)
        FROM
            employees) difference
FROM
    employees
ORDER BY first_name , last_name;

執行上面查詢語句，得到以下結果 -

+-------------+------------+-----------+--------+----------------+------------+
| employee_id | first_name | last_name | salary | average_salary | difference |
+-------------+------------+-----------+--------+----------------+------------+
|         103 | Alexander  | Lee       | 9000   | 8060           | 940        |
|         115 | Alexander  | Su        | 3100   | 8060           | -4960      |
|         114 | Avg        | Su        | 11000  | 8060           | 2940       |
|         193 | Britney    | Zhao      | 3900   | 8060           | -4160      |
|         104 | Bruce      | Wong      | 6000   | 8060           | -2060      |
|         179 | Charles    | Yang      | 6200   | 8060           | -1860      |
|         109 | Daniel     | Chen      | 9000   | 8060           | 940        |
|         105 | David      | Liang     | 4800   | 8060           | -3260      |
... ... 
|         192 | Sarah      | Yang      | 4000   | 8060           | -4060      |
|         123 | Shanta     | Liu       | 6500   | 8060           | -1560      |
|         205 | Shelley    | Wu        | 12000  | 8060           | 3940       |
|         116 | Shelli     | Zhang     | 2900   | 8060           | -5160      |
|         117 | Sigal      | Zhang     | 2800   | 8060           | -5260      |
|         100 | Steven     | Lee       | 24000  | 8060           | 15940      |
|         203 | Susan      | Zhou      | 6500   | 8060           | -1560      |
|         106 | Valli      | Chen      | 4800   | 8060           | -3260      |
|         206 | William    | Wu        | 8300   | 8060           | 240        |
+-------------+------------+-----------+--------+----------------+------------+
40 rows in set

通過上面的學習，現在您應該了解SQL子查詢是什麼，以及如何使用子查詢來形成靈活的SQL語句。