Exists operator is a special type of operator which is used in a subquery. It is used to check whether a subquery returns something or not. If a subquery returns some row then outer query get executed otherwise the whole query will return nothing.
We can use EXISTS operator with DELETE , UPDATE OR DELETE statements other than SELECT statement.
WHERE EXISTS (SELECT *
WHERE Employee.Emp_id = Payroll.P_id);
This query will return rows only if the inner query will get executed successfully (return some data).
Example of Exists operator with Delete query:
DELETE FROM Employee WHERE
EXISTS (SELECT * FROM Payroll WHERE
Payroll.p_id = Employee.Emp_id);