Database Reference
In-Depth Information
12.4.4
Regular versus Correlated Subqueries
This section discusses the pros and cons of using regular or correlated sub-
queries.
A correlated subquery allows the correlation or matching of a column
between a calling query and a subquery. The calling query can pass an
aliased column name into the subquery, not the other way around. Queries
are parsed from left to right and from top to bottom. The SQL parser will
not understand what to do with an attempt to pass a column alias from
bottom to top and will produce a syntax (SQL parse) error. A subquery is
parsed and executed before its calling query or subquery. For example, the
following query has a SELECT clause that references a column from a cor-
related subquery found in the WHERE clause. The following query passes
the ARTIST_ID column value from the calling query into the subquery,
matching each ARTIST table row with related STUDIOTIME table rows.
SELECT A.NAME FROM ARTIST A
WHERE '01-JAN-2000' > ALL
(SELECT ST.SESSION_DATE FROM STUDIOTIME ST
WHERE ST.ARTIST_ID = A.ARTIST_ID);
The most common use for correlated subqueries is using the EXISTS
comparison condition as in the script shown following. The ARTIST_ID
column value is passed from the calling query into the subquery. A correla-
Figure 12.12
Values Can Be
Passed from a
Calling Query into
a Correlated
Subquery.
