MySQL
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.
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.
SELECT column_name FROM table_name WHERE EXISTS ( SELECT some_column_name FROM some_table_name WHERE some_condition )
For this tutorial we will be using the employee and comments table.
employee
comments
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)
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.
c
e
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.
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;
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.