Oracle 5.0, Справочное руководство

Страница: 1127 / 3232

Subquery Syntax
1107
(SELECT * FROM t2 LIMIT 1);
• Replace a join with a subquery. For example, try this:
SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
SELECT column1 FROM t2);
Instead of this:
SELECT DISTINCT t1.column1 FROM t1, t2
WHERE t1.column1 = t2.column1;
• Some subqueries can be transformed to joins for compatibility with older versions of MySQL that
do not support subqueries. However, in some cases, converting a subquery to a join may improve
performance. See
Section 13.2.9.11, “Rewriting Subqueries as Joins” .
• Move clauses from outside to inside the subquery. For example, use this query:
SELECT * FROM t1
WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
Instead of this query:
SELECT * FROM t1
WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);
For another example, use this query:
SELECT (SELECT c 5 FROM t1) FROM t2;
Instead of this query:
SELECT (SELECT column1 FROM t1) + 5 FROM t2;
• Use a row subquery instead of a correlated subquery. For example, use this query:
SELECT * FROM t1
WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
Instead of this query:
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
AND t2.column2=t1.column2);
• Use
NOT (a = ANY (...))
rather than
a <> ALL (...)
.
• Use
x = ANY (table containing (1,2))
rather than
x=1 OR x=2
.
• Use
= ANY
rather than
EXISTS
.
• For uncorrelated subqueries that always return one row,
IN
is always slower than
=
. For example,
use this query:
SELECT * FROM t1
WHERE t1. col_name = (SELECT a FROM t2 WHERE b = some_const );
Instead of this query:
SELECT * FROM t1
WHERE t1. col_name IN (SELECT a FROM t2 WHERE b = some_const );
These tricks might cause programs to go faster or slower. Using MySQL facilities like the
BENCHMARK()
[957] function, you can get an idea about what helps in your own situation. See
Section 12.13, “Information Functions” .
Some optimizations that MySQL itself makes are: