![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961128.webp)
UPDATE
Syntax
1108
• MySQL executes uncorrelated subqueries only once. Use
EXPLAIN
to make sure that a given
subquery really is uncorrelated.
• MySQL rewrites
IN
,
ALL
,
ANY
, and
SOME
subqueries in an attempt to take advantage of the
possibility that the select-list columns in the subquery are indexed.
• MySQL replaces subqueries of the following form with an index-lookup function, which
EXPLAIN
describes as a special join type (
unique_subquery
[647]
or
index_subquery
[647]
):
... IN (SELECT
indexed_column
FROM
single_table
...)
• MySQL enhances expressions of the following form with an expression involving
MIN()
[971]
or
MAX()
[971]
, unless
NULL
values or empty sets are involved:
value
{ALL|ANY|SOME} {> | < | >= | <=} (
uncorrelated subquery
)
For example, this
WHERE
clause:
WHERE 5 > ALL (SELECT x FROM t)
might be treated by the optimizer like this:
WHERE 5 > (SELECT MAX(x) FROM t)
See also
MySQL Internals: How MySQL Transforms Subqueries
.
13.2.9.11. Rewriting Subqueries as Joins
Sometimes there are other ways to test membership in a set of values than by using a subquery. Also,
on some occasions, it is not only possible to rewrite a query without a subquery, but it can be more
efficient to make use of some of these techniques rather than to use subqueries. One of these is the
IN()
construct:
For example, this query:
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
Can be rewritten as:
SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;
The queries:
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);
Can be rewritten as:
SELECT table1.*
FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
A
LEFT [OUTER] JOIN
can be faster than an equivalent subquery because the server might be able
to optimize it better—a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did
not exist, so subqueries were the only way to do certain things. Today, MySQL Server and many other
modern database systems offer a wide range of outer join types.
MySQL Server supports multiple-table
DELETE
statements that can be used to efficiently delete rows
based on information from one table or even from many tables at the same time. Multiple-table
UPDATE
statements are also supported. See
Section 13.2.2, “
DELETE
Syntax”
, and
Section 13.2.10, “
UPDATE
Syntax”
.
13.2.10.
UPDATE
Syntax
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...