Rechercher dans le manuel MySQL
13.2.11.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:
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:
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:
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.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-correlated-subqueries.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.