Subquery Syntax
1103
AND cities_stores.store_type = stores.store_type));
The last example is a double-nested
NOT EXISTS
query. That is, it has a
NOT EXISTS
clause within
a
NOT EXISTS
clause. Formally, it answers the question “does a city exist with a store that is not in
Stores
”? But it is easier to say that a nested
NOT EXISTS
answers the question “is
x
TRUE
for all
y
?”
13.2.9.7. Correlated Subqueries
A correlated subquery is a subquery that contains a reference to a table that also appears in the outer
query. For example:
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
Notice that the subquery contains a reference to a column of
t1
, even though the subquery's
FROM
clause does not mention a table
t1
. So, MySQL looks outside the subquery, and finds
t1
in the outer
query.
Suppose that table
t1
contains a row where
column1 = 5
and
column2 = 6
; meanwhile, table
t2
contains a row where
column1 = 5
and
column2 = 7
. The simple expression
... WHERE
column1 = ANY (SELECT column1 FROM t2)
would be
TRUE
, but in this example, the
WHERE
clause within the subquery is
FALSE
(because
(5,6)
is not equal to
(5,7)
), so the expression as a
whole is
FALSE
.
Scoping rule: MySQL evaluates from inside to outside. For example:
SELECT column1 FROM t1 AS x
WHERE x.column1 = (SELECT column1 FROM t2 AS x
WHERE x.column1 = (SELECT column1 FROM t3
WHERE x.column2 = t3.column1));
In this statement,
x.column2
must be a column in table
t2
because
SELECT column1 FROM t2
AS x ...
renames
t2
. It is not a column in table
t1
because
SELECT column1 FROM t1 ...
is
an outer query that is farther out.
For subqueries in
HAVING
or
ORDER BY
clauses, MySQL also looks for column names in the outer
select list.
For certain cases, a correlated subquery is optimized. For example:
val
IN (SELECT
key_val
FROM
tbl_name
WHERE
correlated_condition
)
Otherwise, they are inefficient and likely to be slow. Rewriting the query as a join might improve
performance.
Aggregate functions in correlated subqueries may contain outer references, provided the function
contains nothing but outer references, and provided the function is not contained in another function or
expression.
13.2.9.8. Subqueries in the
FROM
Clause
Subqueries are legal in a
SELECT
statement's
FROM
clause. The actual syntax is:
SELECT ... FROM (
subquery
) [AS]
name
...
The
[AS] name
clause is mandatory, because every table in a
FROM
clause must have a name. Any
columns in the
subquery
select list must have unique names.
For the sake of illustration, assume that you have this table:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
