Database Reference
In-Depth Information
Figure 12.8
= SOME with a
Subquery.
This example returns the names of artists who have not been in the stu-
dio after January 1, 2000. The subquery is a correlated multiple-row sub-
query using the ALL comparison condition.
Note:
Note: If you want to list the session date in your query results, you
must use a join or a FROM clause subquery (inline view) instead of a sub-
query in the WHERE clause.
The result is shown in Figure 12.9.
SELECT A.NAME FROM ARTIST A WHERE '01-JAN-2000' > ALL
(SELECT ST.SESSION_DATE FROM STUDIOTIME ST
WHERE ST.ARTIST_ID = A.ARTIST_ID);
12.4.3
Multiple-Column Subqueries
A multiple-column subquery can return a single or multiple rows. It simply
returns more than one column for each row. Typically, a multiple-column
subquery is used to validate a set of columns against another set of columns
in a WHERE clause or as a tuned FROM clause row filter (inline view), as
shown in the two examples following.
The first example following uses the IN set membership comparison to
find a row set of two columns from the ARTIST table where the name of the
