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.
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.
A set subquery returns a set of one or more rows. You use set subqueries in the following contexts.
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.
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.
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 |