The simplest type of subquery is a noncorrelated subquery that returns a single value.

The following PROC SQL query is the same query that is used in the previous section. This query displays job codes for which the group's average salary exceeds the company's average salary. The HAVING clause contains a noncorrelated subquery.

proc sql;
   select jobcode, 
          avg(salary) as AvgSalary 
          format=dollar11.2
      from sasuser.payrollmaster
      group by jobcode
      having avg(salary) >
         (select avg(salary)
            from sasuser.payrollmaster);

PROC SQL always evaluates a noncorrelated subquery before the outer query. If a query contains noncorrelated subqueries at more than one level, PROC SQL evaluates the innermost subquery first and works outward, evaluating the outermost query last.

In the query shown above, the inner query and outer query are processed as follows:

The query output is shown here.

This noncorrelated subquery returns only a single value, the average salary for the whole company, to the outer query. Both the subquery query and the outer query use the same table as a source.

Some subqueries are multiple-value subqueries: they return more than one value (row) to the outer query. If your noncorrelated subquery might return a value for more than one row, be sure to use one of the following operators in the WHERE or HAVING clause that can handle multiple values:

Consider a query that contains both the conditional operator IN and a noncorrelated subquery that returns multiple values. (The operators ANY, ALL, and EXISTS are presented later in this chapter.)

Suppose you want to send birthday cards to employees who have birthdays coming up. You decide to create a PROC SQL query that lists the names and addresses of all employees who have birthdays in February. This query, unlike the one shown on the previous page, selects data from two different tables:

In both tables, the employees are identified by their employee identification number (EmpID).

In the following PROC SQL query, the WHERE clause contains the conditional operator IN followed by a noncorrelated subquery:

 proc sql;
    select empid, lastname, firstname, 
           city, state
       from sasuser.staffmaster
       where empid in
          (select empid
             from sasuser.payrollmaster
             where month(dateofbirth)=2);

This query is processed as follows:

The output, shown below, lists the six employees who have February birthdays.

Although an inner join would have generated the same results, it is better to use a subquery in this example since no columns from the sasuser.payrollmaster table were in the output.

Sometimes it is helpful to compare a value with a set of values returned by a subquery. When a subquery might return multiple values, you must use one of the conditional operators ANY or ALL to modify a comparison operator in the WHERE or HAVING clause immediately before the subquery. For example, the following WHERE clause contains the less than (<) comparison operator and the conditional operator ANY:

where dateofbirth < any
   <subquery...>

When the outer query contains a comparison operator that is modified by ANY or ALL, the outer query compares each value that it retrieves against the value(s) returned by the subquery. All values for which the comparison is true are then included in the query output. If ANY is specified, then the comparison is true if it is true for any one of the values that are returned by the subquery. If ALL is specified, then the comparison is true only if it is true for all values that are returned by the subquery.

Consider how the operators ANY or ALL are used.

An outer query that specifies the ANY operator selects values that pass the comparison test with any of the values that are returned by the subquery.

For example, suppose you have an outer query containing the following WHERE clause:

where dateofbirth < any
   <subquery...>

This WHERE clause specifies that DateofBirth (the operand) should be less than any (the comparison operator) of the values returned by the subquery.

The following chart shows the effect of using ANY with these common comparison operators: greater than (>), less than (<) and equal to (=).

Suppose you want to identify any flight attendants at level 1 or level 2 who are older than any of the flight attendants at level 3. Job type and level are identified in JobCode; each flight attendant has the job code FA1, FA2, or FA3. The following PROC SQL query accomplishes this task by using a subquery and the ANY operator:

proc sql;
   select empid, jobcode, dateofbirth
      from sasuser.payrollmaster
      where jobcode in ('FA1','FA2')
            and dateofbirth < any
               (select dateofbirth
                  from sasuser.payrollmaster
                  where jobcode='FA3'),

Here is what happens when this query is processed:

Note that both the outer query and subquery use the same table.

Below are the query results.

where jobcode in ('FA1','FA2') 
      and dateofbirth < 
         (select max(dateofbirth)
            from [...]

For more information about the MAX function, see the SAS documentation.

An outer query that specifies the ALL operator selects values that pass the comparison test with all of the values that are returned by the subquery.

The following chart shows the effect of using ALL with these common comparison operators: greater than (>) and less than (<).

Substitute ALL for ANY in the previous query example. The following query identifies level-1 and level-2 flight attendants who are older than all of the level-3 flight attendants:

proc sql;
   select empid, jobcode, dateofbirth
      from sasuser.payrollmaster
      where jobcode in ('FA1','FA2')
            and dateofbirth < all
               (select dateofbirth
                  from sasuser.payrollmaster
                  where jobcode='FA3'),

Here is what happens when this query is processed:

The query results, below, show that only two level-1 or level-2 flight attendants are older than all of the level-3 flight attendants.

where jobcode in ('FA1','FA2') 
      and dateofbirth < 
        (select min(dateofbirth) 
           from [...]

For more information about the MIN function, see the SAS documentation.