Rechercher dans le manuel MySQL
C.4 Restrictions on Subqueries
In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
Exception: The preceding prohibition does not apply if for the modified table you are using a derived table and that derived table is materialized rather than merged into the outer query. (See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.) Example:
Here the result from the derived table is materialized as a temporary table, so the relevant rows in
t
have already been selected by the time the update tot
takes place.In general, you may be able to influence the optimizer to materialize a derived table by adding a
NO_MERGE
optimizer hint. See Section 8.9.3, “Optimizer Hints”.Row comparison operations are only partially supported:
For
,expr
[NOT] INsubquery
expr
can be ann
-tuple (specified using row constructor syntax) and the subquery can return rows ofn
-tuples. The permitted syntax is therefore more specifically expressed asrow_constructor
[NOT] INtable_subquery
For
,expr
op
{ALL|ANY|SOME}subquery
expr
must be a scalar value and the subquery must be a column subquery; it cannot return multiple-column rows.
In other words, for a subquery that returns rows of
n
-tuples, this is supported:But this is not supported:
The reason for supporting row comparisons for
IN
but not for the others is thatIN
is implemented by rewriting it as a sequence of=
comparisons andAND
operations. This approach cannot be used forALL
,ANY
, orSOME
.Subqueries in the
FROM
clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so they cannot be evaluated per row of the outer query. The optimizer delays materialization until the result is needed, which may permit materialization to be avoided. See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.MySQL does not support
LIMIT
in subqueries for certain subquery operators:MySQL permits a subquery to refer to a stored function that has data-modifying side effects such as inserting rows into a table. For example, if
f()
inserts rows, the following query can modify data:This behavior is an extension to the SQL standard. In MySQL, it can produce nondeterministic results because
f()
might be executed a different number of times for different executions of a given query depending on how the optimizer chooses to handle it.For statement-based or mixed-format replication, one implication of this indeterminism is that such a query can produce different results on the master and its slaves.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-subquery-restrictions.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.