EXISTS Predicate

Tests whether a subquery returns one or more rows.

Syntax

[NOT] EXISTS (select-statement)

Arguments

select-statement

specifies a subquery with the SELECT statement.

See SELECT Statement

Details

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');

See Also

Statements:
Last updated: February 23, 2017