EXISTS Predicate
Function
This predicate tests to see if a particular resultant table, as specified by the given subquery, actually exists. The resultant table will exist if any resultant rows were identified.
Invocation
One of the six predicates which constitute a search term.
Syntax
EXISTS (QUERY specification)
where query specification is the subquery whose resultant table is to be tested for existence.
Description
Because the resultant table nature does not matter, the subquery may specify a derived column list of any desired cardinality and of any number of resultant rows. The exists predicate only evaluates whether the resultant table does or does not exist. It does not evaluate the derived column list.
If the resultant table does exist, the predicate is true; otherwise it is false. The predicate never equates to unknown.
WHERE 'op' is any valid COMPARISON predicate operator:
WHERE x op (SELECT y FROM t)WHERE EXISTS (SELECT * from t WHERE x op y)
Note:
After the transformation, the subquery no longer has to have a single value result.
WHERE x op ANY (SELECT y FROM t) WHERE EXISTS (SELECT * FROM t WHERE x op y);
WHERE NOT x op ALL (SELECT y FROM t) WHERE NOT EXISTS (SELECT * FROM t WHERE x NOT op y);
WHERE NOT a op ALL (SELECT y FROM t) WHERE EXISTS (SELECT * FROM t WHERE x NOT op y)
Note:
You can only do the second third and fourth example transformations if x and y cannot have a NULL value result.
Example
The following example selects all cruises where the destination harbor is not a starting point for any other cruise:
SELECT cruise_id FROM cruise x
WHERE NOT EXISTS (SELECT * FROM cruise
WHERE x.destination_harbor = start_harbor) ;