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

EXISTS运算符

我们使用EXISTS运算符检查子查询中是否存在任何记录。
如果子查询中至少有一条记录可用，则此运算符将返回TRUE。
否则，我们将得到FALSE。

EXISTS语法

SELECT column_name
FROM table_name
WHERE EXISTS (
    SELECT some_column_name
    FROM some_table_name
    WHERE some_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)

例子1

在此示例中，我们将显示所有至少评论过一次的员工的详细信息。

假设我们要显示进行评论的员工的员工编号，名字和姓氏。

因此，要解决此问题，我们的子查询将找到被评论的员工，如下所示。

mysql> SELECT 
    c.employeeid
FROM
    comments c
WHERE
    c.employeeid = e.employeeid;

其中，" c"是注释表的别名，" e"是雇员表的别名。

现在，我们将列出所有至少评论过一次的员工的详细信息。

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

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

因此，我们可以看到有3名员工至少发表了一次评论。

示例2

在下面的示例中，我们将列出所有至少评论过两次的员工的详细信息。

因此，要解决此问题，我们必须使用聚合函数COUNT来查找员工发布的评论总数。
而且，我们还必须按employeeid将结果分组。

下面列出了子查询以列出所有评论至少两次的雇员编号。

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

其中，c是注释表的别名，e是雇员表的别名。

现在，我们将列出所有发表至少两次评论的员工。

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

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

因此，有两名员工至少发表了2次评论。