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運算符的含義:

條件

描述

c > ALL(…)

c列中的值必須大於要評估爲true的集合中的最大值。

c >= ALL(…)

c列中的值必須大於或等於要評估爲true的集合中的最大值。

c < ALL(…)

c列中的值必須小於要評估爲true的集合中的最小值。

c <= ALL(…)

c列中的值必須小於或等於要評估爲true的集合中的最小值。

c <> ALL(…)

c列中的值不得等於要評估爲true的集合中的任何值。

c = ALL(…)

c列中的值必須等於要評估爲true的集合中的任何值。

2. SQL All運算符示例

我們將使用示例數據庫中的employees表進行演示:

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

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

例如,以下語句查找工資大於部門ID2的員工最高工資的所有員工:

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

執行上面查詢語句,得到以下結果:

SQL All運算符

下面通過查詢部門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;

執行上面查詢語句,得到以下結果:

SQL All運算符

如上圖中所示,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;

執行以上查詢語句,得到以下結果:

SQL All運算符

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;

執行以上查詢語句,得到以下結果:

SQL All運算符

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運算符來測試值是否與子查詢返回的值集匹配。