Documentation Home
MySQL Restrictions and Limitations
Related Documentation Download this Excerpt
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.5Mb
EPUB - 186.0Kb
HTML Download (TGZ) - 121.4Kb
HTML Download (Zip) - 140.0Kb

MySQL Restrictions and Limitations  /  Restrictions on Subqueries

Chapter 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:

    DELETE FROM t WHERE ... (SELECT ... FROM t ...);
    UPDATE t ... WHERE col = (SELECT ... FROM t ...);

    Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

    UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);

    Here the result from the subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

  • Row comparison operations are only partially supported:

    • For expr [NOT] IN subquery, expr can be an n-tuple (specified using row constructor syntax) and the subquery can return rows of n-tuples. The permitted syntax is therefore more specifically expressed as row_constructor [NOT] IN table_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:

    (expr_1, ..., expr_n) [NOT] IN table_subquery

    But this is not supported:

    (expr_1, ..., expr_n) op {ALL|ANY|SOME} subquery

    The reason for supporting row comparisons for IN but not for the others is that IN is implemented by rewriting it as a sequence of = comparisons and AND operations. This approach cannot be used for ALL, ANY, or SOME.

  • 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. Before MySQL 5.6.3, materialization takes place before evaluation of the outer query. As of 5.6.3, the optimizer delays materialization until the result is needed, which may permit materialization to be avoided. See Optimizing Derived Tables (Subqueries) in the FROM Clause.

  • MySQL does not support LIMIT in subqueries for certain subquery operators:

    mysql> SELECT * FROM t1
        ->   WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
    ERROR 1235 (42000): This version of MySQL doesn't yet support
     'LIMIT & IN/ALL/ANY/SOME subquery'
  • 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:

    SELECT ... WHERE x IN (SELECT f() ...);

    This behavior is an extension to the SQL standard. In MySQL, it can produce indeterminate 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.

  • Before MySQL 5.6.3, a subquery in the FROM clause is evaluated by materializing the result into a temporary table, and this table does not use indexes. As of 5.6.3, the optimizer creates an index on the materialized table if this will result in faster query execution. See Optimizing Derived Tables (Subqueries) in the FROM Clause.

Download this Excerpt
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.5Mb
EPUB - 186.0Kb
HTML Download (TGZ) - 121.4Kb
HTML Download (Zip) - 140.0Kb
User Comments
Sign Up Login You must be logged in to post a comment.