ALLBASE/SQL Reference Manual (36216-90216)

120 Chapter3
SQL Queries
Complex Queries
The indicated duplicate rows would have been removed if the example contained theUNION
statement instead of UNION ALL.
Subqueries
A subquery, also known as a nested query, is a query block that is completely embedded in
a predicate. A subquery may appear within the search condition which is a part of the
WHERE or HAVING clause of a SELECT, INSERT, UPDATE or DELETE statement. It is like any
other query expression, except that it cannot contain a UNION operator. A subquery may be
used only in the following types of predicates:
• Comparison predicate
• EXISTS predicate
• IN predicate
• Quantified predicate
Subqueries can be used to arrive at a single value that lets you determine the selection
criteria for the outer query block. In the following simple example, the subquery (in
parentheses) is evaluated to determine a single value used in selecting the rows for the
outer query:
SELECT *
FROM PurchDB.SupplyPrice
WHERE PartNumber = (SELECT PartNumber
FROM PurchDB.Parts
WHERE PartName = 'Cache Memory Unit')
Subqueries are most frequently found within special predicates, which are described fully
in the next section. Additional examples of subqueries can be found there.
Special Predicates
The three types of special predicate are listed here:
• The quantified predicate (ALL, ANY, or SOME), used to compare the value of an
expression with some or all of the values of an operand.
• The IN predicate, used to check for inclusion of an expression in a set of values.
• The EXISTS predicate, used to check for the existence of a value in an operand.
With all these types, subqueries may be used; for ALL, ANY, SOME, and IN predicate,
additional forms allow the use of a value list in place of a subquery. For each type of special
predicate the examples in the next sections show both subquery and non-subquery forms
of the predicate whenever both possibilities exist.
Quantified Predicate
A quantified predicate compares a value with a number of other values that are either
contained in a value list or derived from a subquery. The quantified predicate has the
following general form:
Expression ComparisonOperator Quantifier { ValueListSubQuery }