In this post, let us learn about the options exists and not exists in MySQL.
To understand this better, we will make use of the following two tables in the post.
Both tables are having the same columns with common and uncommon records.
mysql> select * from Employee_details;
+----------+--------+--------+
| Dep_name | Emp_id | Salary |
+----------+--------+--------+
| Computer | 564 | 1400 |
| Computer | 123 | 2500 |
| Computer | 943 | 3200 |
| History | 987 | 3450 |
| Economy | 456 | 4500 |
| Economy | 678 | 6700 |
| Economy | 789 | 7200 |
| Computer | 324 | 2500 |
+----------+--------+--------+
8 rows in set (0.00 sec)mysql> select * from Company;
+----------+--------+--------+
| Dep_name | Emp_id | Salary |
+----------+--------+--------+
| Computer | 564 | 1400 |
| Computer | 943 | 3200 |
| History | 987 | 3450 |
| Economy | 456 | 4500 |
| History | 987 | 3450 |
+----------+--------+--------+
5 rows in set (0.00 sec)How to use?
The below option filters data from the first table if it is not present in the subquery.
The following records are present in the Employee details and not in the Company.
mysql> select * from Employee_details a where not exists (select * from Company
b where a.Emp_id =b.Emp_id );
+----------+--------+--------+
| Dep_name | Emp_id | Salary |
+----------+--------+--------+
| Computer | 123 | 2500 |
| Economy | 678 | 6700 |
| Economy | 789 | 7200 |
| Computer | 324 | 2500 |
+----------+--------+--------+
4 rows in set (0.00 sec)Exists option filters the data from the first table if it presents in the subquery.
The following records present in both tables Employee_details and Company.
mysql> select * from Employee_details a where exists (select * from Company b wh
ere a.Emp_id =b.Emp_id );
+----------+--------+--------+
| Dep_name | Emp_id | Salary |
+----------+--------+--------+
| Computer | 564 | 1400 |
| Computer | 943 | 3200 |
| History | 987 | 3450 |
| Economy | 456 | 4500 |
+----------+--------+--------+
4 rows in set (0.00 sec)Reference
https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html