EXISTS Operator

Details

The EXISTS operator is an operator whose right operand is a subquery. The result of an EXISTS operator is true if the subquery resolves to at least one row. The result of a NOT EXISTS operator is true if the subquery evaluates to zero rows. For example, the following query subsets Proclib.Payroll based on the criteria in the subquery. See Creating a Table from a Query's Result. If the value for Staff.Idnum is on the same row as the value CT in Proclib.Staff, then the matching Idnum in Proclib.Payroll is included in the output. See Joining Two Tables. 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');
quit;