What is the difference between IN and EXISTS?

The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.

Employee Table

emp_id emp_name salary dept_id manager_id
E1 Rahul 15000 D1 M1
E2 Manoj 15000 D1 M1
E3 James 55000 D2 M2
E4 Michael 25000 D2 M2
E5 Ali 20000 D10 M3
E6 Robin 35000 D10 M3

Example

The following SQL statement selects all employees that have ‘D1’ or ‘D10’ as department id.

SELECT * FROM Employee
WHERE dept_id IN ('D1','D10');

Output

emp_id emp_name salary dept_id manager_id
E1 Rahul 15000 D1 M1
E2 Manoj 15000 D1 M1
E5 Ali 20000 D10 M3
E6 Robin 35000 D10 M3

The SQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.

Department Table

dept_id dept_name
D1 IT
D2 HR
D3 Finance
D4 Admin

Example

The following SQL statement returns TRUE and lists the departments which have employees

SELECT dept_name
FROM Department
WHERE EXISTS (SELECT dept_id FROM Employee
WHERE Employee.dept_id = Department.dept_id )

Output

dept_name
IT
HR