SQL EXISTS Operator

The EXISTS condition is considered “to be met” if the subquery returns at least one row. EXISTS operator simply tests whether the inner query returns any row. If it does, then the outer query proceeds. If not, the outer query does not execute, and the entire SQL statement returns nothing.

Syntax:Select columns from tables where exists (subquery); 

Retrieve the entire employee Names and Empno in all the departments.

SQL> SELECT empno, eName FROM Emp e WHERE EXISTS (SELECT ename FROM dept WHERE e.deptno = dept.deptno);

For each row from the EMP table, the condition is checked whether there exists a row in EMP table that has the employees.

• List all the employees detail who do not manage anyone

SQL> SELECT ENAME, JOB FROM EMP E WHERE NOT EXISTS (SELECT * FROM EMP WHERE E.MGR=E.EMPNO); 

Output will be the detail of all the employees such as their name and job.