首頁 » SQL 教學 » SQL 子查詢

SQL 子查詢

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

SQL 子查詢基礎

請參考來自範例資料庫的以下 employees 和 departments 表格

SQL Subquery: Sample tables

假設您必須找出所有位於 id 為 1700 的地點的員工。您可能會想到以下解決方案。

首先，找出所有位於 id 為 1700 的地點的部門。

SELECT 
    *
FROM
    departments
WHERE
    location_id = 1700;
Code language: SQL (Structured Query Language) (sql)
SQL Subquery - department list

其次，使用先前查詢的部門 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;Code language: SQL (Structured Query Language) (sql)
SQL Subquery - IN operator

此解決方案有兩個問題。首先，您查看了 departments 表格，以檢查哪個部門屬於地點 1700。但是，原始問題並非指任何特定部門；它指的是地點 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;Code language: SQL (Structured Query Language) (sql)

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

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

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

SQL 子查詢範例

讓我們來看一些使用子查詢的範例，以了解它們如何運作。

SQL 子查詢與 IN 或 NOT IN 運算子

在先前的範例中，您已經看到了如何將子查詢與 IN 運算子一起使用。以下範例將子查詢與 NOT IN 運算子一起使用，以找出所有不位於地點 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;Code language: SQL (Structured Query Language) (sql)
SQL Subquery - NOT IN operator

SQL 子查詢與比較運算子

以下語法說明如何將子查詢與比較運算子一起使用

comparison_operator (subquery)
Code language: SQL (Structured Query Language) (sql)

其中，比較運算子是這些運算子之一

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

以下範例找出薪資最高的員工

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary = (SELECT 
            MAX(salary)
        FROM
            employees)
ORDER BY first_name , last_name;Code language: SQL (Structured Query Language) (sql)
SQL Subquery with the Equal operator

在此範例中，子查詢傳回所有員工的最高薪資，外部查詢則找出薪資等於最高薪資的員工。

以下語句找出所有薪資高於所有員工平均薪資的員工

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            employees);    
Code language: SQL (Structured Query Language) (sql)
SQL Subquery with greater than operator

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

SQL 子查詢與 EXISTS 或 NOT EXISTS 運算子

EXISTS 運算子會檢查子查詢傳回的資料列是否存在。如果子查詢包含任何資料列，則傳回 true。否則，傳回 false。

EXISTS 運算子的語法如下

EXISTS (subquery )
Code language: SQL (Structured Query Language) (sql)

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

NOT EXISTS (subquery)
Code language: SQL (Structured Query Language) (sql)

以下範例找出所有至少有一位員工薪資大於 10,000 的部門

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; 
Code language: SQL (Structured Query Language) (sql)
SQL Subquery with the EXISTS operator

同樣地，以下語句找出所有沒有任何員工薪資大於 10,000 的部門

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;  
Code language: SQL (Structured Query Language) (sql)

SQL Subquery with NOT EXISTS operator

SQL 子查詢與 ALL 運算子

當子查詢與 ALL 運算子一起使用時，其語法如下

comparison_operator ALL (subquery)
Code language: SQL (Structured Query Language) (sql)

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

x > ALL (subquery)
Code language: SQL (Structured Query Language) (sql)

例如，假設子查詢傳回三個值：一、二和三。如果 x 大於 3，則以下條件的計算結果為 true。

x > ALL (1,2,3)
Code language: SQL (Structured Query Language) (sql)

以下查詢使用 GROUP BY 子句和 MIN() 函數來找出每個部門的最低薪資

SELECT 
    MIN(salary)
FROM
    employees
GROUP BY department_id
ORDER BY MIN(salary) DESC;
Code language: SQL (Structured Query Language) (sql)
SQL Subquery - min salary by department

以下範例找出所有薪資高於每個部門最低薪資的員工

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;Code language: SQL (Structured Query Language) (sql)
SQL Subquery with ALL operator example

SQL 子查詢與 ANY 運算子

以下顯示具有 ANY 運算子的子查詢語法

comparison_operator ANY (subquery)
Code language: SQL (Structured Query Language) (sql)

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

x > ANY (subquery)Code language: SQL (Structured Query Language) (sql)

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

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

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= SOME (SELECT 
            MAX(salary)
        FROM
            employees
        GROUP BY department_id);
Code language: SQL (Structured Query Language) (sql)
SQL Subquery with SOME operator example

在此範例中，子查詢找出每個部門中員工的最高薪資。外部查詢會查看這些值，並判斷哪些員工的薪資大於或等於任何部門的最高薪資。

SQL 子查詢在 FROM 子句中

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

SELECT 
    *
FROM
    (subquery) AS table_name
Code language: SQL (Structured Query Language) (sql)

在這個語法中，資料表別名是強制性的，因為 FROM 子句中的所有資料表都必須有名稱。

請注意，FROM 子句中指定的 子查詢在 MySQL 中稱為衍生資料表，在 Oracle 中稱為內嵌視圖。

以下語句傳回每個部門的平均薪資

SELECT 
    AVG(salary) average_salary
FROM
    employees
GROUP BY department_id;
Code language: SQL (Structured Query Language) (sql)
SQL Subquery - average salary by department

您可以使用此查詢作為 FROM 子句中的子查詢，以計算部門平均薪資的平均值，如下所示

SELECT 
    ROUND(AVG(average_salary), 0)
FROM
    (SELECT 
        AVG(salary) average_salary
    FROM
        employees
    GROUP BY department_id) department_salary;
Code language: SQL (Structured Query Language) (sql)
SQL Subquery - average of average salary by department

SQL 子查詢在 SELECT 子句中

子查詢可以用於 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;Code language: SQL (Structured Query Language) (sql)
SQL Subquery in SELECT clause

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

這個教學對您有幫助嗎？
SQL CUBE
上一個
SQL CUBE
下一個
SQL 相關子查詢
SQL Correlated Subquery