EXISTS

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!

Syntax

The basic syntax for the EXISTS function is:

SELECT columns
  FROM tables
 WHERE EXISTS ( subquery );

Example

SELECT dname
  FROM dept a
 WHERE NOT EXISTS (SELECT b.deptno 
                     FROM emp b
                    WHERE a.deptno = b.deptno)