Oracle 5.0, Reference Manual

Page: 1120 / 3232

Subquery Syntax
1100
For example:
... WHERE 'a' = (SELECT column1 FROM t1)
MySQL also permits this construct:
non_subquery_operand LIKE ( subquery )
At one time the only legal place for a subquery was on the right side of a comparison, and you might
still find some old DBMSs that insist on this.
Here is an example of a common-form subquery comparison that you cannot do with a join. It finds all
the rows in table
t1
for which the
column1
value is equal to a maximum value in table
t2
:
SELECT * FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);
Here is another example, which again is impossible with a join because it involves aggregating for one
of the tables. It finds all rows in table
t1
containing a value that occurs twice in a given column:
SELECT * FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);
For a comparison of the subquery to a scalar, the subquery must return a scalar. For a comparison of
the subquery to a row constructor, the subquery must be a row subquery that returns a row with the
same number of values as the row constructor. See
Section 13.2.9.5, “Row Subqueries” .
13.2.9.3. Subqueries with
ANY
,
IN
, or
SOME
Syntax:
operand comparison_operator ANY ( subquery )
operand IN ( subquery )
operand comparison_operator SOME ( subquery )
Where
comparison_operator
is one of these operators:
= > < >= <= <> !=
The
ANY
keyword, which must follow a comparison operator, means “return
TRUE
if the comparison is
TRUE
for
ANY
of the values in the column that the subquery returns.” For example:
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
Suppose that there is a row in table
t1
containing
(10)
. The expression is
TRUE
if table
t2
contains
(21,14,7)
because there is a value
7
in
t2
that is less than
10
. The expression is
FALSE
if table
t2
contains
(20,10)
, or if table
t2
is empty. The expression is unknown (that is,
NULL
) if table
t2
contains
(NULL,NULL,NULL)
.
When used with a subquery, the word
IN
is an alias for
= ANY
. Thus, these two statements are the
same:
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
IN
and
= ANY
are not synonyms when used with an expression list.
IN
can take an expression list, but
= ANY
cannot. See Section 12.3.2, “Comparison Functions and Operators” .
NOT IN
is not an alias for
<> ANY
, but for
<> ALL
. See Section 13.2.9.4, “Subqueries with
ALL
” .
The word
SOME
is an alias for
ANY
. Thus, these two statements are the same:
SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);