NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-82
Considerations—Subqueries
an EXISTS predicate or if the FROM clause refers to a single table (or view)
consisting of a single column.
°
A subquery cannot contain an ORDER BY clause.
°
If a subquery is not part of an EXISTS, IN, or quantified predicate, and the
subquery evaluates to more than one row, a run-time error occurs.
°
If a subquery contains references to an outer query, the subquery might be
evaluated repeatedly. This type of subquery is called a correlated subquery,
discussed later in this subsection.
•
Nested subqueries
An outer query (a main SELECT statement) can have up to 15 levels of nested
subqueries.
Subqueries within the same ON, WHERE, or HAVING clause are at the same level.
For example, the following query has one level of nesting:
SELECT * FROM TABLE1
WHERE A = (SELECT P FROM TABLE2 WHERE Q = 1)
AND B = (SELECT X FROM TABLE3 WHERE Y = 2)
A subquery within the WHERE clause of another subquery defines a new level, so
the following query has two levels of nesting:
SELECT * FROM TABLE1
WHERE A = (SELECT P FROM TABLE2
WHERE Q = (SELECT X FROM TABLE3
WHERE Y = 2))
•
Correlated subqueries
In the search condition of a subquery, you can refer to columns of any table or view
defined in an outer query. Such a reference is called an outer reference. A subquery
containing an outer reference is called a correlated subquery.
Each time an outer query selects and evaluates a row, the outer reference is visible
as a new value to the correlated subquery. The correlated subquery operates on the
new value to test its own search condition, so the correlated subquery executes
whenever the outer query selects a new row, which leads to reduced performance.
If you refer to a column name that occurs in more than one outer query, you must
qualify the column name with the correlation name of the table or view to which it
belongs. The correlation name is known to other subqueries at the same level or to
inner queries but not to outer queries.
If you use the same correlation name at different levels of nesting, an inner query
uses the name from the nearest outer level. SQL checks the FROM clause of the
subquery first, then its outer query, and so forth, until it determines the applicable
table or view.