EXISTS and NOT EXISTS are SQL conditions/functions that executes a subquery and return either TRUE or FALSE depending on if rows were found or not. EXISTS can only be used in the WHERE clause of a query. The subquery can refer to columns in the parent query (called a correlated subquery).
Note that EXISTS will not scan all rows in the subquery, only one row is required to determine whether the outcome is TRUE or FALSE. However, NOT EXISTS must scan all rows, which may cause performance problems!
The basic syntax for the EXISTS function is:
SELECT columns FROM tables WHERE EXISTS ( subquery );
SELECT dname FROM dept a WHERE NOT EXISTS (SELECT b.deptno FROM emp b WHERE a.deptno = b.deptno)