# SQL All運算子

#### 1. SQL ALL運算子簡介

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

``````WHERE column_name comparison_operator ALL (subquery)
``````

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

`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運算子範例

2.1. SQL ALL使用大於運算子

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

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

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

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

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

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

2.3. SQL ALL使用小於運算子

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

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

``````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小於或等於運算子

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

``````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與不等於運算子

``````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
``````

2.6. SQL ALL與等於運算子

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

``````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
``````