Database Reference
In-Depth Information
tion or match is drawn between the ARTIST.ARTIST_ID and GUESTAP-
PEARANCE.GUESTARTIST_ID column values. This query is a variation
on a similar query shown previously in this chapter in Figure 12.6. The
query in Figure 12.6 is slightly different in that it uses NOT EXISTS as
opposed to EXISTS.
SELECT NAME FROM ARTIST A WHERE EXISTS
(SELECT GA.GUESTARTIST_ID
FROM GUESTAPPEARANCE GA
WHERE GA.GUESTARTIST_ID = A.ARTIST_ID);
Regular subqueries maintain no relationship or correlation between the
calling query and the subquery. A regular subquery will execute before the
calling query such that the calling query will operate on the result set pro-
duced by the subquery. You cannot reference any columns within the sub-
query from the calling query. For example, this query has a regular
subquery, a variation on the query in Figure 12.12 except excluding the cor-
related columns, passed from the calling query into the subquery.
SELECT S.RECORDING_DATE FROM SONG S
WHERE S.RECORDING_DATE > ALL
(SELECT ST.SESSION_DATE FROM STUDIOTIME ST);
Regardless of when Oracle parses the subquery, the calling query cannot
contain references to any columns that belong to the subquery. The only
exception to this rule is when the subquery is in the FROM clause. In that
case, the subquery columns are available to the calling query and can be
used in the SELECT and WHERE clauses of the calling query.
12.4.5
Nested Subqueries
A nested subquery is a subquery nested or buried within another subquery.
For example, the following query has a nested subquery executed against
the CDTRACK table, called from the subquery executed against the MUS-
ICCD table. The result is shown in Figure 12.13.
SELECT GENRE FROM GENRE WHERE GENRE_ID IN
(SELECT GENRE_ID FROM MUSICCD WHERE MUSICCD_ID IN
(SELECT MUSICCD_ID FROM CDTRACK));
