MySQL - EXISTS

In this tutorial we will learn about the EXISTS operator in MySQL.

We learned about subquery in the previous tutorial. So, feel free to check that out as we are going to use that in this tutorial.

The EXISTS operator

We use the EXISTS operator to check the existence of any record in the subquery. And this operator returns TRUE if there is at least one record available in the subquery. Otherwise we get FALSE.

EXISTS syntax

SELECT column_name
FROM table_name
WHERE EXISTS (
    SELECT some_column_name
    FROM some_table_name
    WHERE some_condition
)

Tables

For this tutorial we will be using the employee and comments table.

Table: employee

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

Table: comments

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

Example #1

In this example we will display details of all those employees who commented at least once.

Lets say we want to show the employeeid, firstname and lastname of the employee who commented.

So, to solve this our subquery to find the employee who commented will look like the following.

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

Where, c is the alias for the comments table and e is the alias for the employee table.

Now we will list the details of all the employees who commented at least once.

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        | Yusuf     | Shakeel  |
| e03        | Jane      | Doe      |
| e04        | Tin       | Tin      |
+------------+-----------+----------+
3 rows in set (0.00 sec)

So, we can see that there are 3 employees who commented at least once.

Example #2

In the following example we will list the details of all the employees who commented at least twice.

So, to solve this problem we have to use the aggregate function COUNT to find the total number of comments posted by an employee. And we also have to group the result by employeeid.

Subquery to list all the employeeid who commented at least twice is given below.

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

Where, c is the alias for the comments table and e is the alias for the employee table.

Now, we will list all the employees who commented at least twice.

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        | Yusuf     | Shakeel  |             2 |
| e03        | Jane      | Doe      |             2 |
+------------+-----------+----------+---------------+
2 rows in set (0.00 sec)

So, there are two employees who commented at least 2 times.