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語句。