以下のサブクエリーは異なったクエリーを実行しているが、結果は同じになります。

※ ともにそれぞれの大陸で1番大きい国を選んでます

例1)
以下の例は、同じテーブルを利用している訳ではなく
外部、サブクエリーの依存関係はありません。
サブクエリーだけでも成り立ちます。
しかしサブクエリーは独立していて、且つ複数の結果を
返すため ”=”だけでは成り立たない為、”= ANY”
を付けて複数のサブクエリーが返す結果に対応しています。


select Continent,Name,SurfaceArea from Country
where surfaceArea = ANY
(select max(SurfaceArea)
from Country group by Continent);

サブクエリーだけでも成り立ちます。しかし複数列を返すのでANYを利用しています。
mysql> select max(SurfaceArea) from Country group by Continent;
+——————+
| max(SurfaceArea) |
+——————+
| 9572900.00 |
| 17075400.00 |
| 9970610.00 |
| 2505813.00 |
| 7741220.00 |
| 13120000.00 |
| 8547403.00 |
+——————+
7 rows in set (0.00 sec)

mysql>

ANYを利用しないと以下のようエラーになります。

mysql> select Continent,Name,SurfaceArea from Country
-> where surfaceArea =
-> (select max(SurfaceArea)
-> from Country group by Continent);
ERROR 1242 (21000): Subquery returns more than 1 row

mysql>

————————————————————————————————————

例2)
自己結合を用いている。
サブクエリーだけでは成り立たず、外部のQueryと結合して初めて答えが出る。

SELECT C.Continent, C.Name, C.SurfaceArea
FROM Country C
WHERE SurfaceArea =(
SELECT MAX(SurfaceArea)
FROM Country C2
WHERE C2.Continent = C.Continent);

サブクエリーだけだと以下のようにエラーになります。
mysql> SELECT MAX(SurfaceArea)
-> FROM Country C2
-> WHERE C2.Continent = C.Continent;
ERROR 1054 (42S22): Unknown column 'C.Continent' in 'where clause'

mysql>

sub_same

※ ともにそれぞれのCityテーブルから、町田を抽出しています。


SELECT Name,Population,District FROM City
WHERE (Name, District, CountryCode) = ('Machida', 'Tokyo-to', 'JPN');


SELECT Name,Population,District FROM City
WHERE CountryCode = 'JPN' and District ='Tokyo-to' and Name ='Machida';

subquery_same

実行プランはどちらも同じです。

execution_plan1

参考サイト
MYSQL サブクエリー(row constructors)


比較演算子の後に続かなければいけない ANY キーワードは、
「もしサブクエリが返すカラム内の値の ANY に対する比較が TRUE であれば、
TRUE を返す」 という事を意味します。

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

(例) SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

テーブル (10) を含むテーブル t1 内に行があると仮定してください。
10 以下である値 7 が t2 の中にあるので、もしテーブル t2 が (21,14,7) を含むなら、
その式は TRUE です。もしテーブル t2 が (20,10) を含むか、テーブル t2 が空であれば、
その式は FALSE です。もしテーブル t2 が (NULL,NULL,NULL) を含むなら、その式は
UNKNOWN です。

サブクエリと共に利用される時、IN という言葉は = ANY のエイリアスとなります。
従って、これら2つのステートメントは同じになります。


SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

■GROUP BYでまとめず単純に平均面積より大きいデータを選択した場合。

SELECT Continent, Name
FROM Country
WHERE SurfaceArea > (SELECT AVG(SurfaceArea)
FROM Country );

select_any_group_by

■”GROUP BY Continent”でANYを付けずにSELECTした場合は、複数列が返って
きてエラーになります。ANYを付ける事で複数行が返ってくるサブクエリーの処理も
問題無く処理できます。


SELECT Continent, Name
FROM Country
WHERE SurfaceArea > ANY (SELECT AVG(SurfaceArea)
FROM Country
GROUP BY Continent
);

select_any

    参考サイト


12.2.8.3. ANY、IN、そして SOME を持つサブクエリ

D.3. サブクエリの規制