SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-29
Correlated Subqueries
You can combine these two queries into a single query that contains a subquery, as
follows:
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE) ;
The SELECT that appears on the right-hand side of the predicate is the subquery,
sometimes called an inner query. The other SELECT, sometimes called the outer
SELECT, uses the value or set of values computed by the subquery.
The select list of a subquery must contain only one element unless the subquery is part
of an EXISTS predicate or a quantified predicate (ANY or ALL), described under
Defining Predicates on page 1-30.
The two types of subqueries, correlated and noncorrelated, are described next. A
subquery can also be quantified or nonquantified, depending on the type of predicate
specified; for more information about the latter, see Quantified Predicates on
page 1-38.
The use of subqueries can have an impact on performance; for more information, see
Section 3, Improving Query Performance Through Query Design.
Correlated Subqueries
A correlated subquery references values from the outer query. For example, the
subquery uses values returned by the outer query and is, therefore, a correlated
subquery.
SELECT item_name, retail_price
FROM INVNTRY outer
WHERE retail_price >
(SELECT AVG(retail_price)
FROM INVNTRY
WHERE producer = outer.producer) ;
Noncorrelated Subqueries
A noncorrelated subquery does not reference or depend on the result of the outer
query. The subquery is performed only once for the query, instead of being performed
once for each qualifying outer table row. This subquery uses two instances of the
EMPLOYEE table, EMP1 and EMP2, and is a noncorrelated subquery:
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE EMP1
Note. The preceding example associates the correlation name OUTER with the first
occurrence of the INVNTRY table. This technique is also used in subsequent examples. 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.