SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-53
Noncorrelated Subquery
A subquery is dependent on the outer query if the subquery is correlated to the outer
query, as shown:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY OUTER
WHERE RETAIL_PRICE > SELECT AVG(RETAIL_PRICE)
FROM INVNTRY
WHERE PRODUCER = OUTER.PRODUCER
This example selects information on items that cost more than the average price of the
items produced by the same producer. The subquery is dependent on the outer
SELECT because it references the PRODUCER column of a row retrieved for the
outer SELECT. This correlation forces the evaluation of the subquery for every row
retrieved from the outer SELECT. The overall query is more expensive to evaluate
because of the repeated evaluation of the subquery.
Noncorrelated Subquery
A noncorrelated subquery does not reference or depend on the result of the outer
query. Consequently, a noncorrelated subquery can be evaluated once and the results
used repeatedly for the outer query.
This example contrasts the performance of the correlated subquery with that of a
noncorrelated subquery. The subquery in this example executes only once to select
rows that satisfy the specified search condition:
SELECT emp_id, first_name, last_name, mgr_id
FROM employee emp1
WHERE emp_id IN (SELECT mgr_id
FROM employee emp2) ;
The subquery is evaluated to determine MGR_ID, and MGR_ID is then substituted in
the predicate.
The noncorrelated subquery returns the same result as the correlated subquery in the
preceding example, but with fewer system resources:
EMP_ID FIRST_NAME LAST_NAME MGR_ID
------ ---------- --------- ------
2705 Travis Simpson 6554
2906 Etsuro Nakagawa 6554
3598 Eichiro Nakamura 2906
9069 John Smith 2705
--- 4 row(s) selected.
The subquery is independent of the outer SELECT because it can be evaluated
without any knowledge of the result of the outer SELECT. This independence allows
the subquery to be evaluated only once.
For a cost analysis of correlated and noncorrelated subqueries, see Section 5,
Selectivity and Cost Estimates.