Neoview SQL Reference Manual (R2.4 SP2)

Subquery
A subquery is a query expression enclosed in parentheses. Its syntactic form is specified in the
syntax of a SELECT statement. For further information about query expressions, see “SELECT
Statement” (page 163).
A subquery is used to provide values for a BETWEEN, comparison, EXISTS, IN, or quantified
comparison predicate in a search condition. It is also used to specify a derived table in the FROM
clause of a SELECT statement.
A subquery can be a table, row, or scalar subquery. Therefore, its result table can be a table
consisting of multiple rows and columns, a single row of column values, or a single row consisting
of only one column value.
SELECT Form of a Subquery
A subquery is typically specified as a special form of a SELECT statement enclosed in parentheses
that queries (or selects) to provide values in a search condition or to specify a derived table as a
table reference.
The form of a subquery specified as a SELECT statement is query-expr.
Neither the ORDER BY clause nor [FIRST N] / [ANY N] clause is allowed in a subquery.
Using Subqueries to Provide Comparison Values
When a subquery is used to provide comparison values, the SELECT statement that contains the
subquery is called an outer query. The subquery within the SELECT is called an inner query. In
this case, the differences between the SELECT statement and the SELECT form of a subquery
are:
• A subquery is always enclosed in parentheses.
• A subquery cannot contain an ORDER BY clause.
• If a subquery is not part of an EXISTS, IN, or quantified comparison predicate, and the
subquery evaluates to more than one row, a run-time error occurs.
Nested Subqueries When Providing Comparison Values
An outer query (a main SELECT statement) can have up to 15 levels of nested subqueries.
Subqueries within the same WHERE or HAVING clause are at the same level. For example, this
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 is at a different level, however, so
this 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 When Providing Comparison Values
In the search condition of a subquery, when you refer to columns of any table or view defined
in an outer query, the reference is called an outer reference. A subquery containing an outer
reference is called a correlated subquery.
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. Similarly, if you
refer to a column name that occurs in the subquery and in one or more outer queries, you must
qualify the column name with the correlation name of the table or view to which it belongs. The
294 SQL Language Elements