The EXISTS predicate
is an operator whose right operand is a subquery. The result of an
EXISTS predicate is true if the subquery resolves to at least one
row. The result of a NOT EXISTS predicate is true if the subquery
evaluates to zero rows.
Example
The following query
subsets PAYROLL based on the criteria in the subquery. If the value
for STAFF.IDNUM is on the same row as the value CT in STAFF, then
the matching IDNUM in PAYROLL is included in the output. Thus, the
query returns all the employees from PAYROLL who live in CT.
select *
from payroll p
where exists (select * from staff s
where p.idnumber=s.idnum and state='CT');