Database Reference
In-Depth Information
Figure 12.5
The IN
Comparison
Condition with No
Rows Returned in
the Subquery.
This query lists artists who never made guest appearances on any songs.
The subquery is a correlated multiple-row subquery and uses the NOT
EXISTS comparison condition. The result is shown in Figure 12.6.
SELECT NAME FROM ARTIST A WHERE NOT EXISTS
(SELECT GA.GUESTARTIST_ID
FROM GUESTAPPEARANCE GA
WHERE GA.GUESTARTIST_ID = A.ARTIST_ID);
This query returns the names of artists who recorded songs before May
1, 2001. The subquery is a regular multiple-row subquery using the ANY
comparison condition. If you want to list the recording date in your query
results, you must use a join or a FROM clause subquery. The result is
shown in Figure 12.7.
SELECT NAME FROM ARTIST A WHERE A.ARTIST_ID = ANY
(SELECT S.ARTIST_ID FROM SONG S
WHERE S.RECORDING_DATE < '01-MAY-2001');
This query returns the titles of CDs that have songs with a guest appear-
ance. The subquery is a regular multiple-row subquery using the SOME
comparison condition (SOME is identical to ANY). The result is shown in
Figure 12.8.
