在本教程中，我们将学习MySQL中的ANY和ALL运算符。

ANY运算符

如果子查询返回的任何值的比较为TRUE，则ANY运算符将返回TRUE。

ANY语法

SELECT column_name
FROM table_name
WHERE
  some_column comparison_operator ANY (
    SELECT some_other_column
    FROM some_other_table
    WHERE some_other_condition
  )

其中" comparison_operator"是任何比较运算符，例如" =，<，>，<=，> =，！=，<>"。

ALL运算符

如果子查询返回的所有值的比较为TRUE，则ALL运算符将返回TRUE。

ALL语法

SELECT column_name
FROM table_name
WHERE
  some_column comparison_operator ALL (
    SELECT some_other_column
    FROM some_other_table
    WHERE some_other_condition
  )

示例表

在本教程中，我们将使用"员工"和"评论"表。

表：员工

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        |      |   | @example.com    |     7 | 1900-01-01 | 2016-02-04 06:08:10 | 2016-01-01 01:01:01 |
| e02        | John      | Doe      | [email protected]  |     8 | 1900-02-03 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e03        | Jane      | Doe      | [email protected]  |     9 | 1900-05-20 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e04        | Tin       | Tin      | [email protected]   |     6 | 1900-10-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:03 |
| e05        | Bob       | Coder    | [email protected] |     7 | 1900-08-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

表：评论

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2016-01-02 01:02:03 | 2016-01-02 01:02:03 |
|         3 | e01        | Happy               | 2016-01-02 01:04:03 | 2016-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2016-04-04 03:02:01 | 2016-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

示例：ANY运算符

在下面的示例中，我们将显示那些至少评论过两次的员工的详细信息。

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid = ANY (SELECT
            c.employeeid
        FROM
            comments c
        GROUP BY c.employeeid
        HAVING COUNT(c.commentid) >= 2);

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01        |      |   |
| e03        | Jane      | Doe      |
+------------+-----------+----------+
2 rows in set (0.00 sec)

说明：

如果子查询返回的任何c.employeeid等于employee表的e.employeeid且c.employeeid至少评论了两次，则上面的查询将列出雇员的详细信息。

子查询返回以下值。

mysql> SELECT
    c.employeeid
FROM
    comments c
GROUP BY c.employeeid
HAVING COUNT(c.commentid) >= 2;

+------------+
| employeeid |
+------------+
| e01        |
| e03        |
+------------+
2 rows in set (0.00 sec)

因此，我们的查询如下。

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid = ANY ('e01', 'e03');

这意味着，如果" e.employeeid"等于子查询的两个值中的任何一个，则将列出该雇员的详细信息。

示例：ALL运算符

在以下示例中，我们将仅列出员工从未发表评论的详细信息。

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid != ALL (SELECT DISTINCT
            c.employeeid
        FROM
            comments c);

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e02        | John      | Doe      |
| e05        | Bob       | Coder    |
+------------+-----------+----------+
2 rows in set (0.01 sec)

上面的查询提供了从未评论的雇员ID" e02"和" e05"的详细信息。

说明：

对于给定的子查询，我们得到以下输出。

mysql> SELECT DISTINCT
    c.employeeid
FROM
    comments c;

+------------+
| employeeid |
+------------+
| e01        |
| e03        |
| e04        |
+------------+
3 rows in set (0.00 sec)

因此，雇员编号" e01"，" e03"和" e04"至少评论了一次。

替换上面的值，我们将得到以下查询。

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid != ALL ('e01', 'e03', 'e05');

因此，上述查询返回了雇员编号不是" e01"，" e03"和" e05"的那些雇员的详细信息。