Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.2.10.7 相関サブクエリー

相関サブクエリーは、外部クエリーにも現れるテーブルへの参照を含むサブクエリーです。例:

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

このサブクエリーには、サブクエリーの FROM 句でテーブル t1 が指定されていない場合でも、t1 のカラムへの参照が含まれます。そのため、MySQL はこのサブクエリーの外部を探し、外部クエリー内の t1 を見つけます。

テーブル t1column1 = 5 かつ column2 = 6 である行が含まれている一方、テーブル t2column1 = 5 かつ column2 = 7 である行が含まれているとします。単純な式 ... WHERE column1 = ANY (SELECT column1 FROM t2)TRUE になりますが、この例では、サブクエリー内の WHERE 句は ((5,6)(5,7) に等しくないため) FALSE です。そのため、全体としての式は FALSE です。

スコープルール: MySQL は、内部から外部に評価します。例:

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3
      WHERE x.column2 = t3.column1));

このステートメントでは、SELECT column1 FROM t2 AS x ...t2 の名前を変更するため、x.column2 はテーブル t2 内のカラムである必要があります。SELECT column1 FROM t1 ...さらに外部にある外部クエリーであるため、これはテーブル t1 内のカラムではありません。

HAVING または ORDER BY 句内のサブクエリーの場合、MySQL は外部選択リスト内でもカラム名を探します。

特定のケースでは、相関サブクエリーが最適化されます。例:

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

そうしないと、これらは非効率的であり、遅くなる可能性があります。クエリーを結合として書き換えると、パフォーマンスが向上することがあります。

相関サブクエリー内の集約関数には、その関数に外部参照以外は何も含まれておらず、かつその関数が別の関数または式に含まれていない場合、外部参照を含めることができます。


User Comments
  Posted by Are you mortal Then prepare to die. on November 24, 2004
Another example of when a subquery is optimized is when using a subquery based on a multipart primary key to 'join' the two tables.

For example

[sql]
DELETE FROM t1 WHERE ROW(c1,c2) IN (
SELECT c1, c2 FROM t2
);
[/sql]

You could easily select the above 'join' using a 'where t1.c1=t2.c1 and t1.c2=t2.c2', but a delete statement won't know what to do (and fails) over the joined tables.
  Posted by Ryan Findley on January 11, 2006
Note that according to bug #9090 ( http://bugs.mysql.com/bug.php?id=9090 ) it appears that ALL subqueries will be executed as correlated subqueries until version 5.2 or 6.0, when the optimizer may gain the ability to correctly execute uncorrelated subqueries.
  Posted by Stephen Dewey on August 18, 2006
Ryan Findley is good to point out the bug report, but as far as I can tell it is not true that MySQL rewrites ALL subqueries as correlated subqueries. Rather, the problem that the bug report identifies is that the MySQL optimizer does that to subqueries that test an IN statement. This is listed as one of the current limitations of MySQL's subquery support here (third item down, "Subquery optimization for IN..."):

http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html

So if you are writing a subquery that does not use IN, I think that you will be able to keep it uncorrelated. However, I have not tested this.
  Posted by Tobias F on April 12, 2007
If you have a slow 'correlated' subquery with IN, you can optimize it with a join to get around the bug described by Ryan and Stephen. After the optimization the execution time is no longer O(M×N).

the queries are made up for this example.
original query (execution time 4 sec):
SELECT * FROM employee WHERE room = 15 OR
room IN (
SELECT room FROM assistant WHERE building = 11
)

optimized query (execution time 0,004 sec):
SELECT e1.* FROM employee e1
INNER JOIN (
SELECT room FROM assistant WHERE building = 11
) ea ON e1.room = ea.room
UNION SELECT * FROM employee WHERE room = 15

if you have a simple query like
SELECT * FROM employee WHERE room IN (
SELECT room FROM assistant WHERE building = 11
)
just leave the UNION operator
Sign Up Login You must be logged in to post a comment.