ALLBASE/SQL Reference Manual (36216-90216)
Chapter 3 117
SQL Queries
Complex Queries
You can create a complex query by using the following:
• UNION operator, which allows you to take the union of all rows returned by several
query blocks in one SELECT statement.
• Subqueries (also known as nested queries), which allow you to embed a query block
within the search condition of an outer SELECT statement.
• Special predicates, such as ANY, ALL, SOME, EXISTS, and IN, which allow you to
compare the value of an expression with the value of special structures and subqueries.
The next sections describe each type of complex query with examples.
UNION Queries
A SELECT statement can consist of several query blocks connected by UNION or UNION ALL
statements. Each individual SELECT statement returns a query result which is a set of
rows selected from a specified table or tables. The union of these query results is presented
as a table that consists of all rows appearing in one or more of the original query results.
If only the UNION statement is used, all duplicate rows are removed from the final set of
rows. In this case, the maximum size of a tuple in the query result is given by the following
formula:
(
SelectListItems
+1)*2 + (
SumListLengths
) <= 4000
where:
SelectListItems
is the number of items in the select list.
SumListLengths
is the sum of the lengths of all the columns in the select list.
At compile time,
SumKeyLengths
is computed assuming columns of NULL and VARCHAR
contain no data. At run time, the actual data lengths are assumed.
If the UNION ALL operator is used, duplicates are not removed. Candidates for duplicate
removal are evaluated by comparing entire tuples, not just a single field. Only if two or
more rows are entirely alike are the duplicates removed. In the case of the UNION ALL
operator, the maximum size of a tuple in the query result is 3996 bytes, as it is for a
non-UNION query expression. You cannot use LONG columns in a UNION statement.
Suppose you wanted to find out the part number for all parts that require 30 days or more
for delivery, or are supplied by the vendor whose number is 9002. The following query
delivers this information using the UNION form of the SELECT statement:
SELECT PartNumber
FROM PurchDB.SupplyPrice
WHERE DeliveryDays >= 30
UNION
SELECT PartNumber
FROM PurchDB.SupplyPrice
WHERE VendorNumber = 9002