ALLBASE/SQL Reference Manual (36216-90216)

502 Chapter12
SQL Statements S - Z
SELECT
Parameters — Subquery Level
QueryExpression is the basic syntax of a query or SELECT statement. The query
expression in a subquery may not contain any UNION or UNION ALL
operations.
Description — Subquery Level
• Subqueries are used to retrieve data that is then used in evaluating a search condition.
For example, get supplier numbers for the suppliers who supply the maximum quantity
of part 'P1'.
SELECT SP.SNO
FROM SP
WHERE SP.PNO = 'P1'
AND SP.QTY = ( SELECT MAX(SP.QTY)
FROM SP
WHERE SP.PNO = 'P1')
Without using nested queries, the same answer would require the two following queries
— one to find the maximum, the other to list the supplier number:
SELECT MAX(SP.QTY)
FROM SP
WHERE SP.PNO = 'P1'
and
SELECT SP.SNO
FROM SP
WHERE SP.PNO = 'P1'
AND SP.QTY =
MaxQty
where MaxQty is the result of the first query.
• A subquery may be used only in the following types of predicates:
• EXISTS predicate.
• Quantified predicate.
• IN predicate.
• Comparison predicate.
• A subquery may be used in the WHERE or HAVING clause of SELECT statements and
in the WHERE clause of UPDATE, INSERT, and DELETE statements.
• A subquery may also be nested in the WHERE or HAVING clause of another subquery.
No ALLBASE/SQL statement can have more than 16 query blocks within it.
• A subquery may reference a column value in a higher level of the query (or outer
query). Such a reference is called an outer reference. A subquery making an outer
reference is called a correlated subquery. Because a correlated subquery depends on a
value of the outer query, the subquery must be reevaluated for each new value of the
outer query, as in the following example to get supplier numbers for those who supply
the most parts for each part number.