EXISTS Operator:

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.

Syntax:

SELECT columnname(s)
FROM tablename
WHERE EXISTS
(SELECT *
FROM tablename2"
WHERE "condition");

We can use EXISTS operator with DELETE , UPDATE OR DELETE statements other than SELECT statement.

Example:

SELECT *
FROM Employee
WHERE EXISTS (SELECT *
              FROM Payroll
              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);