Database Reference
In-Depth Information
Figure 12.9
> ALL with a
Subquery.
artist contains a lowercase letter “u”. See the result in Figure 12.10. Notice
that the subquery SELECT clause contains two columns. Notice also that the
calling query WHERE clause filter has a list, in parentheses, of two columns
that are to be compared to the two columns returned by the subquery.
SELECT A.ARTIST_ID, A.NAME, S.TITLE FROM ARTIST A, SONG S
WHERE (A.ARTIST_ID, A.NAME) IN
(SELECT ARTIST_ID, NAME FROM ARTIST
WHERE NAME LIKE '%u%') AND A.ARTIST_ID = S.ARTIST_ID;
The next and second example of a multiple-column subquery will pro-
duce the same result as shown in Figure 12.10. In Figure 12.11, an element
of the FROM clause contains the same subquery as in the first example in
Figure 12.10.
Note:
This example is better than the previous one for a very large ARTIST
table because the filter is executed before the join of the ARTIST rows with
the SONG rows. The query will perform better because the join occurs on
a smaller number of rows.
1
