ALLBASE/SQL Reference Manual (36216-90216)

126 Chapter3
SQL Queries
Complex Queries
Correlated Versus Noncorrelated Subqueries
In many cases, it is possible to execute the subquery just once, and obtain a result which is
passed to the outer query for its use. Here is an example:
SELECT *
FROM PurchDB.SupplyPrice
WHERE PartNumber = (SELECT PartNumber
FROM PurchDB.Parts
WHERE PartName = 'Cache Memory Unit')
This kind of subquery is a noncorrelated subquery.
In other cases, however, it is necessary to evaluate a subquery once for every row in the
outer query, as in the following:
SELECT v.VendorName
FROM PurchDB.Vendors v
WHERE NOT EXISTS (SELECT *
FROM PurchDB.SupplyPrice sp
WHERE sp.VendorNumber = v.VendorNumber)
The predicate in the subquery references the column value v.VendorNumber, which is
defined by the outer query block. When this type of relationship exists between a column
value in the subquery and a column value in an outer query block, the query is called a
correlated subquery.
Recognizing correlated subqueries is important when performance is a priority. Correlated
subqueries require the optimizer to use an outer loop join algorithm rather than a
sort-merge join. Because a sort-merge join is orders of magnitude faster than an outer loop
join, correlated subqueries pay a performance penalty. In addition, when the ANY, SOME,
ALL, or IN predicate makes use of subqueries, the queries are converted into correlated
subqueries using the EXISTS predicate. Therefore, if at all possible, queries using ANY,
SOME, ALL, IN, or the correlated form of the EXISTS predicate should be done as joins of
two or more tables rather than by using subqueries if performance is an issue. In fact, it is
possible to state a query as a join as well as in a form using subqueries; non-correlated
subqueries are faster than sort-merge joins. Sort-merge joins are faster than correlated
subqueries which use an outer loop join.
Outer Joins
An inner join returns only tuples for which matching values are found between the
common columns in the joined tables. A natural inner join specifies that each pair of
common columns is coalesced into a single column in the query result. The term join has
become synonymous with the term natural inner join because that type of join is used so
frequently.
To include in the query result those tuples from one table for which there is no match in
the common columns of the other table you use an outer join. The term natural, when
applied to an outer join, has the same meaning as with an inner join. Common columns are
coalesced into a single column in the query result. No duplicate columns are returned.