Database Reference
In-Depth Information
Here is an easy way of understanding the concept of the single-row sub-
query. You can ask if “Apple Pie” equals “Apple Pie,” but you cannot ask if
“Apple Pie” is equal to both “Apple Pie” and “Pumpkin Pie” because you get
two different answers at once. Apple pie is equal to apple pie but not equal
to pumpkin pie. The same applies to testing for a number, say 10, being
BETWEEN five other numbers because it does not make sense. For exam-
ple, 10 BETWEEN 5 AND (20, 4, 30) cannot be evaluated because it is
both true and false. The same applies to the LIKE clause because a single
LIKE comparison condition can only be used to match a single pattern, not
many patterns.
Following is an example of a single-row subquery. The ROWNUM
pseudocolumn is used to restrict the subquery to a single row no matter
how many rows it returns. See the result in Figure 12.2.
SELECT SONG_ID, GUESTARTIST_ID, INSTRUMENT_ID
FROM INSTRUMENTATION WHERE INSTRUMENT_ID =
(SELECT INSTRUMENT_ID FROM INSTRUMENT
WHERE NAME = 'Acoustic Guitar');
In the next example, the query in Figure 12.2 is altered to ensure that
multiple rows are returned from the subquery. Removing the WHERE
clause filter from the query in Figure 12.2 results in an error, as shown in
Figure 12.3. The subquery in Figure 12.3 returns all rows in the INSTRU-
MENT table.
Figure 12.2
A Single-Row
Subquery.