The SQL Procedure

EXISTS condition

Tests if a subquery returns one or more rows.

See also: Query Expressions (Subqueries)

<NOT> EXISTS (query-expression)

query-expression is described in query-expression .

Details
The EXISTS condition is an operator whose right operand is a subquery. The result of an EXISTS condition is true if the subquery resolves to at least one row. The result of a NOT EXISTS condition is true if the subquery evaluates to zero rows. For example, the following query subsets PROCLIB.PAYROLL (which is shown in Creating a Table from a Query's Result ) based on the criteria in the subquery. If the value for STAFF.IDNUM is on the same row as the value CT in PROCLIB.STAFF (which is shown in Joining Two Tables ), the matching IDNUM in PROCLIB.PAYROLL is included in the output. Thus, the query returns all the employees from PROCLIB.PAYROLL who live in CT.

 proc sql; 
   select *  
     from proclib.payroll p  
     where exists (select *  
                      from proclib.staff s 
                      where p.idnumber=s.idnum  
                            and state='CT');

Chapter Contents
Previous
Next
Top of Page