SQL EXISTS Operator

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)

SQL EXISTS Operator – Interview Questions

Q 1: What is EXISTS used for?

Ans: It checks if a subquery returns rows.

Q 2: Does EXISTS return data?

Ans: No, it returns true or false.

Q 3: Is EXISTS faster than IN?

Ans: Often, yes.

Q 4: Can EXISTS use correlated subqueries?

Ans: Yes.

Q 5: Is EXISTS used in WHERE?