ALLBASE/SQL Reference Manual (36216-90216)

ManualsBrandsHP ManualsSoftwareMPE/iX 6.5 Operating System

501

502

503

504

505

506

507

508

509

510

Chapter 12 503

SQL Statements S - Z

SELECT

SELECT SP1.SNO

FROM SP SP1

WHERE SP1.QTY = (SELECT MAX(SP2.QTY)

FROM SP SP2

WHERE SP1.PNO = SP2.PNO)

Note that the reference to SP1.PNO in the WHERE clause of the subquery is an outer

reference. In this case, because both the outer query and the subquery refer to table SP,

correlation names SP1 and SP2 are assigned to make the distinction between the outer

and normal references. Within the subquery, any unqualiﬁed column names (that is,

those which are speciﬁed without a table name) are assumed to refer only to tables

speciﬁed in the FROM clause of that subquery.

• If a query has a HAVING clause with subqueries in it, any outer reference made from

those subqueries to the query with the HAVING clause must refer to a column speciﬁed

in a GROUP BY clause.

SQL Syntax — Query Expression Level

{QueryBlock

(QueryExpression)} [UNION [ALL] {QueryBlock

(QueryExpression}][...]

Parameters — Query Expression Level

QueryBlock

is the primary query stating which tables to query, which columns to

return, and which search conditions to use for ﬁltering data. The query

block is further described in one of the next sections.

UNION unites two query expressions into a combined query expression.

The union of two sets is the set of all elements that belong to either or both

of the original sets. Because a table is a set of rows, the union of two tables

is possible. The resulting table consists of all rows appearing in either or

both of the original tables.

ALL indicates that duplicates are not removed from the result table when

UNION is speciﬁed. If UNION is speciﬁed without ALL, duplicates

are

removed.

(

QueryExpression

) may be embedded within another query expression if enclosed in

parentheses. Parentheses are optional when a query expression is not

embedded.

Description — Query Expression Level

• For the following, assume that T1 is the result of the query block or query expression on

the left of the UNION operator, and T2 is the result of the query block or query

expression on the right of the UNION operator. (The same conditions must be met if

there are additional UNION operators which include results from T3, ...T

.):

— T1 and T2 must have the same number of columns. (They may be derived from tables

with varying numbers of columns.)