Databases Reference
In-Depth Information
The syntax of a subquery takes three possible forms, described as follows.
6.7.10.1 Syntax 1
Comparison
[ANY | SOME | ALL] (
SQLStatement
)
where
Comparison
is an expression followed by a comparison relation that compares the
expression with the return value(s) of the subquery. This syntax is used to compare a
value against the values obtained from another query.
For example, the following statement returns all titles and prices of books from the
BOOKS table, whose prices are greater than the maximum price of all books in the table
BOOKS2:
SELECT Title, Price
FROM BOOKS
WHERE Price > (SELECT Max(Price) FROM BOOKS2);
Note that since the subquery returns only one value, we do not need to use any of the
keywords ANY, SOME, or ALL.
The following statement selects all BOOKS titles and prices for books that are more
expensive than ALL of the topic published by Big House:
SELECT Title, Price
FROM BOOKS
WHERE Price > ALL
(SELECT Price
FROM PUBLISHERS INNER JOIN BOOKS ON PUBLISHERS.PubID =
BOOKS.PubID
WHERE PubName = "Big House");
Note that ANY and SOME have the same meaning and return all choices that make the
comparison true for at least one value returned by the subquery. For example, if we were
to replace ALL by SOME in the previous example, the return table would consist of all
book titles and prices for books that are more expensive than the cheapest book published
by Big House.
6.7.10.2 Syntax 2
Expression
[NOT] IN (
SQLStatement
)
This syntax is used to look up a column value in the result table of another query.
For example, the following statement returns all book titles from BOOKS that do not
appear in the table BOOKS2:
SELECT Title
FROM BOOKS
