Oracle 5.0, Reference Manual

Page: 1122 / 3232

Subquery Syntax
1102
an empty result set. The expression is unknown (that is,
NULL
) if the subquery produces no rows. An
error occurs if the subquery produces multiple rows because a row subquery can return at most one
row.
The expressions
(1,2)
and
ROW(1,2)
are sometimes called row constructors. The two are
equivalent. The row constructor and the row returned by the subquery must contain the same number
of values.
A row constructor is used for comparisons with subqueries that return two or more columns. When
a subquery returns a single column, this is regarded as a scalar value and not as a row, so a row
constructor cannot be used with a subquery that does not return at least two columns. Thus, the
following query fails with a syntax error:
SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2)
Row constructors are legal in other contexts. For example, the following two statements are
semantically equivalent:
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
Prior to MySQL 5.0.26, only the second of the preceding two expressions could be optimized. (Bug
#16081)
The following query answers the request, “find all rows in table
t1
that also exist in table
t2
”:
SELECT column1,column2,column3
FROM t1
WHERE (column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2);
13.2.9.6. Subqueries with
EXISTS
or
NOT EXISTS
If a subquery returns any rows at all,
EXISTS subquery
is
TRUE
, and
NOT EXISTS subquery
is
FALSE
. For example:
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
Traditionally, an
EXISTS
subquery starts with
SELECT *
, but it could begin with
SELECT 5
or
SELECT
column1
or anything at all. MySQL ignores the
SELECT
list in such a subquery, so it makes no
difference.
For the preceding example, if
t2
contains any rows, even rows with nothing but
NULL
values, the
EXISTS
condition is
TRUE
. This is actually an unlikely example because a
[NOT] EXISTS
subquery
almost always contains correlations. Here are some more realistic examples:
• What kind of store is present in one or more cities?
SELECT DISTINCT store_type FROM stores
WHERE EXISTS (SELECT * FROM cities_stores
WHERE cities_stores.store_type = stores.store_type);
• What kind of store is present in no cities?
SELECT DISTINCT store_type FROM stores
WHERE NOT EXISTS (SELECT * FROM cities_stores
WHERE cities_stores.store_type = stores.store_type);
• What kind of store is present in all cities?
SELECT DISTINCT store_type FROM stores s1
WHERE NOT EXISTS (
SELECT * FROM cities WHERE NOT EXISTS (
SELECT * FROM cities_stores
WHERE cities_stores.city = cities.city