SQL All運算子

在本教學中，您將了解SQL ALL運算子以及如何使用它來將值與一組值進行比較。

1. SQL ALL運算子簡介

SQL ALL運算子是一個邏輯運算子，它將單個值與子查詢返回的單列值集進行比較。

以下是SQL ALL運算子的語法：

WHERE column_name comparison_operator ALL (subquery)

SQL ALL運算子必須以比較運算子開頭，例如：>，>=，<，<=，<>，=，後跟子查詢。 某些資料庫系統(如Oracle)允許使用文字值列表而不是子查詢。

請注意，如果子查詢不返回任何行，則WHERE子句中的條件始終為true。 假設子查詢返回一行或多行，下表說明了SQL ALL運算子的含義：

2. SQL All運算子範例

我們將使用範例資料庫中的employees表進行演示：

2.1. SQL ALL使用大於運算子
以下查詢查詢column_name列中的值大於子查詢返回的最大值的行：

SELECT 
    *
FROM
    table_name
WHERE
    column_name > ALL (subquery);

例如，以下語句查詢工資大於部門ID為2的員工最高工資的所有員工：

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

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

下面通過查詢部門ID為2中員工的最高薪水來驗證它：

mysql> SELECT 
    MAX(salary)
FROM
    employees
WHERE
    department_id = 2; 
+-------------+
| MAX(salary) |
+-------------+
| 13000       |
+-------------+
1 row in set

此查詢返回13000，該值小於使用上述ALL運算子的查詢返回的任何薪水。

2.2. SQL ALL大於或等於運算子

下面顯示了具有大於或等於運算子的SQL ALL運算子的語法：

SELECT 
    *
FROM
    table_name
WHERE
    column_name >= ALL (subquery);

該查詢返回column_name列中的值大於或等於子查詢返回的所有值的所有行。

例如，以下查詢查詢薪水大於或等於市場行銷部門(department_id=2)員工最高薪水的所有員工：

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

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

如上圖中所示，Michael員工的薪水為13000，等於行銷部門員工的最高薪水包含在結果集中。

2.3. SQL ALL使用小於運算子
以下說明了ALL運算子結合小於運算子一起使用的查詢：

SELECT 
    *
FROM
    table_name
WHERE
    column_name < ALL (subquery);

此查詢返回column_name列中的值小於子查詢返回的最小值的所有行。

以下語句查詢市場行銷部門(department_id=2)中員工的最低薪水：

SELECT 
    MIN(salary)
FROM
    employees
WHERE
    department_id = 2;
+-------------+
| MIN(salary) |
+-------------+
| 6000        |
+-------------+
1 row in set

要查詢薪水低於市場行銷部門(department_id=2)員工最低薪水的所有員工，請使用ALL運算子和小於運算子，如下所示：

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary < ALL (SELECT 
            salary
        FROM
            employees
        WHERE
            department_id = 2)
ORDER BY salary DESC;

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

2.4. SQL ALL小於或等於運算子
以下是ALL運算子與小於或等於運算子一起使用的語法：

SELECT 
    *
FROM
    table_name
WHERE
    column_name <= ALL (subquery);

例如，以下語句查詢薪水小於或等於市場行銷部門(department_id=2)員工最低薪水的所有員工：

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary <= ALL (SELECT 
            salary
        FROM
            employees
        WHERE
            department_id = 2)
ORDER BY salary DESC;

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

2.5. SQL ALL與不等於運算子

以下查詢返回column_name列中的值不等於子查詢返回的任何值的所有行：

SELECT 
    *
FROM
    table_name
WHERE
    column_name <> ALL (subquery);

例如，要查詢工資不等於每個部門平均工資的員工，請使用以下查詢：

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary <> ALL (SELECT 
            AVG(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY salary DESC;

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

+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Steven     | Lee       | 24000  |
| Neena      | Wong      | 17000  |
| Lex        | Liang     | 17000  |
| John       | Liu       | 14000  |
| Karen      | Liu       | 13500  |
| Michael    | Zhou      | 13000  |
| Nancy      | Chen      | 12000  |
| Shelley    | Wu        | 12000  |
| Avg        | Su        | 11000  |
| Alexander  | Lee       | 9000   |
... ...
| Shelli     | Zhang     | 2900   |
| Sigal      | Zhang     | 2800   |
| Irene      | Liu       | 2700   |
| Guy        | Zhang     | 2600   |
| Karen      | Zhang     | 2500   |
+------------+-----------+--------+
35 rows in set

請注意，子查詢通過使用AVG()函式和GROUP BY子句查詢部門員工的平均工資。

2.6. SQL ALL與等於運算子

當將ALL運算子與等於運算子一起使用時，查詢將查詢column_name列中的值等於子查詢返回的任何值的所有行：

SELECT 
    *
FROM
    table_name
WHERE
    column_name = ALL (subquery);

以下範例查詢薪水等於市場行銷部門(department_id=2)員工最高薪水的所有員工：

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary = ALL (SELECT 
            MAX(salary)
        FROM
            employees
        WHERE
            department_id = 2);

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

+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Michael    | Zhou      | 13000  |
+------------+-----------+--------+
1 row in set

在本教學中，您學習了如何使用SQL ALL運算子來測試值是否與子查詢返回的值集匹配。