Database Reference
In-Depth Information
Figure 12.4
The IN
Comparison
Condition.
Following are some multiple-row subquery examples, perhaps allowing for
a better understanding of multiple-row subquery comparison conditions.
This query returns the names of all instruments in the INSTRUMENT
table that are used by artists doing guest appearances. The subquery is a reg-
ular (noncorrelated) multiple-row subquery using the IN comparison con-
dition. The result is shown in Figure 12.4.
SELECT NAME FROM INSTRUMENT
WHERE INSTRUMENT_ID IN
(SELECT INSTRUMENT_ID FROM INSTRUMENTATION);
This query returns the name of instruments played when ARTIST_ID
of 1 made a guest appearance. Because ARTIST_ID 1 made no guest
appearances on any songs, no rows are returned by the subquery. This
shows that a subquery returning a NULL set of rows is valid. The subquery
is a regular, multiple-row subquery using the IN comparison condition.
The result is shown in Figure 12.5.
SELECT NAME FROM INSTRUMENT WHERE INSTRUMENT_ID IN
(SELECT INSTRUMENT_ID FROM INSTRUMENTATION
WHERE GUESTARTIST_ID = 1);
