EXISTS operator is used to at least two tables. EXISTS operator return true when subquery get the one or more records.It is applicable in CRUD (create/read/update/delete) operation.
For SELECT Statement
Syntax:-
SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery)
Example:- Suppose we have two table employees and emp_salary.
employees table:- this table has employee’s details
+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+-----------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 3 | Andrew | Symonds | andrew@abc.com | Australia |
| 4 | Miacle | Tailor | miacle@abc.com | Australia |
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
| 6 | Virat | Kohli | virat@abc.com | India |
| 7 | rohit | NULL | rohit@abc.com | India |
emp_salary table:- this table has employee’s salary details
+----+--------+--------+
| id | emp_id | salary |
+----+--------+--------+
| 1 | 5 | 200000 |
| 2 | 6 | 180000 |
+----+--------+--------+
2 rows in set (0.00 sec)
Find the employee’s details which has a salary.
Query:-
SELECT * FROM employees
WHERE EXISTS
(SELECT * FROM emp_salary WHERE employees.id=emp_salary.emp_id);
Output:-
+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+---------+
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
| 6 | Virat | Kohli | virat@abc.com | India |
+----+------------+-----------+----------------+---------+
2 rows in set (0.00 sec)