EXISTS

The EXISTS predicate tests for the existence of certain rows. The fullselect can specify any number of columns, and can result in true or false.

Notes:

• The outer SELECT list of fullselect must not contain an array value.

The result of the EXISTS predicate:

• Is true only if the number of rows that is specified by the fullselect is not zero.
• Is false only if the number of rows specified by the fullselect is zero.
• Cannot be unknown.

The SELECT clause in the fullselect can specify any number of columns because the values returned by the fullselect are ignored. For convenience, use: SELECT *

Unlike NULL, LIKE, and IN predicates, the EXISTS predicate has no form that contains the word NOT. To negate an EXISTS predicate, precede it with the logical operator NOT, as : NOT EXISTS (fullselect)

The result is then false if the EXISTS predicate is true, and true if the predicate is false. Here, NOT is a logical operator and not a part of the predicate.

Example 1:

The following example compares the values in the column EMPL_NAME of the table EMPLOYEE with the values in the column EMPL_NAME of the table EMPL_COMP and returns the record that has a similar name with age >20.

SELECT T1.EMPL_ID, T1.EMPL_NAME FROM EMPL_COMP T1 
WHERE EXISTS 
SELECT T2.AGE FROM EMPLOYEE T2  WHERE T1.EMPL_NAME = T2.EMPL_NAME AND T2.AGE > 20)

The above example returns the following.