SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-52
Correlated Subquery
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 EMP1 table appears in the FROM clause of the outer query, but its column,
EMP_ID, is referenced in the search condition of the subquery. This type of reference
is called a correlated reference. A correlated reference acts as a placeholder for a
value belonging to the correlated column, in this case, EMP_ID.
When SQL executes the query for each row selected by the outer query, the correlated
reference in the search condition of the subquery is replaced by its corresponding
value. Therefore, if the first row selected by the outer query contains the value 2705 in
the column EMP_ID, the correlated column reference is replaced by that value.
The subquery is then executed, and the row selected by the outer query is returned if
its search condition evaluates to true. The placeholder is refreshed upon the selection
of the next row by the outer query. The subquery is executed again to select rows
satisfying the new search condition.
You can see that a correlated subquery impacts performance adversely because the
subquery is executed every time its search condition changes; that is, it is executed
once for every row selected by the outer query. If the outer query retrieves 10,000
rows, for example, then the correlated subquery executes 10,000 times.
In some cases, however, a correlated subquery might be more efficient than a
noncorrelated subquery. A correlated subquery might be more efficient, for example, if
there are very few rows returned from the outer query and the subquery queries a very
small table.
Suppose that the outer query in the former example retrieves only 5 rows, and an
index exists on the column specified in the inner query (an index exists on MGR_ID). In
this case, evaluation of the correlated subquery can be satisfied with an index-only
access; that is, all columns that the query references can be found in the index. To
retrieve five rows, the index is accessed five times.
Note. The preceding example associates the correlation names EMP1 and EMP2 to different
instances of the EMPLOYEE table. A correlation name is an SQL identifier that you associate
with a table or view. You can define correlation names in the FROM clause of the SELECT
statement. For more information on correlation names, see the SQL/MP Reference Manual.