Progress
SQL-89
Guide and Reference


Using Subqueries

A subquery is a SELECT statement that is nested within a WHERE clause. The result of the subquery is used as a value in the WHERE clause. You must enclose a subquery in parentheses ( ), and it can have only one column in its SELECT list. EXISTS is the exception to this restriction; the aggregate expression in an EXISTS is not evaluated unless the subquery contains a HAVING clause.

There are two types of subqueries: singleton subqueries and set subqueries. A singleton subquery is preceded by a comparison operator and returns, at most, a single row.

You use a singleton subquery in the following context.

SYNTAX
expression operator ( SELECT-statement ) 

The following example contains a singleton subquery in the WHERE clause. The subquery returns the order number of the order with the maximum number of item 51 ordered.

SELECT Order-Mum
  FROM Order-Line
 WHERE Item-Num = 51 AND Qty =
       (SELECT MAX(Qty) FROM Order-Line WHERE Item-Num = 51). 

A set subquery returns a set of one or more rows. You use set subqueries in the following contexts.

SYNTAX
{ expression operator 
  {   ANY | ALL | SOME } ( SELECT-statement )
     | expression [ NOT ] IN ( SELECT-statement )
     |  [ NOT ] EXISTS ( SELECT-statement ) } 

The set subquery options are search conditions that perform the following functions:

When you specify ALL, the search condition is true if the comparison is true for each of the values that the subquery returns. When you specify ANY, the search condition is true if the comparison is true for at least one of the values that the subquery returns. The keyword SOME is equivalent to the keyword ANY.

The subquery can return zero, one, or many values. If it returns zero values, the search condition has a value of TRUE if you specify ALL, FALSE if you specify ANY. If a NULL value is encountered, the condition has a value of FALSE if you specify ALL, UNKNOWN if you specify ANY and no other value matches. See Table 3–1 for a summary of these search condition values using the ANY, ALL, or SOME keywords.

Table 3–1: Search Condition Values  
Subquery Return Value
ALL
ANY
(or SOME)
0
TRUE
FALSE
NULL
FALSE
UNKNOWN

The [ NOT ] IN search condition tests whether the expression is equal, or not equal, to any value that the SELECT statement returns.

The EXISTS search condition determines whether the subquery returns any rows. The search condition is true if the subquery returns one or more rows. (This is similar to the Progress CAN-FIND function.) You can nest EXISTS searches within other EXISTS searches.

The following example lists information about customers who have orders in the order table.

SELECT Name, Cust-Num, City, State
  FROM Customer
  WHERE EXISTS
         (SELECT * FROM Order WHERE Order.Cust-Num = Customer.Cust-Num). 

You can use SELECT * in subqueries only with the EXISTS search condition, even if the result has more than one column. You specify SELECT * for an EXISTS search condition, since no data is actually returned.

Correlated Subqueries

A correlated subquery is a subquery that depends on a value set in the outer query. This example finds the customer with the maximum credit-limit within each state using a correlated subquery.

SELECT Cust-Num, Credit-Limit, State
FROM Customer this_cust
WHERE Credit-Limit = (SELECT MAX(Credit-limit)
                      FROM Customer
                      WHERE this_cust.State = Customer.State). 

The outer query accesses a customer record through the correlation name this_cust. The subquery references that variable to find the maximum credit-limit of any customer in the same state as customer this_cust. The subquery cannot be evaluated until a record is read into this_cust. Therefore, the subquery must be re-evaluated each time the value of this_cust.State changes.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095