MYSQL Subqueryについて

サブクエリーとアウタークエリーの関連性について

Non-correlated subqueryの例
サブクエリーはouter queryを意識せずとも成り立つので、
Correlationはありません。

SELECT DISTINCT Continent
FROM Country
WHERE Code IN (SELECT CountryCode
FROM CountryLanguage
WHERE Language='English'
AND Percentage>50
);


SELECT CountryCode
FROM CountryLanguage
WHERE Language='English'
AND Percentage>50;


以下のQueryもCorrelationはありません。

SELECT * FROM Country
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country
WHERE Continent = 'South America')\G



SELECT MIN(Population) FROM Country
WHERE Continent = 'South America';


上記QueryをCorrelationがあるqueryに書き換えてみる。
変更前

SELECT * FROM Country
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country
WHERE Continent = 'South America')\G


変更後

SELECT * FROM Country as COUNT01
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country COUNT02
WHERE COUNT01.Continent = COUNT02.Continent)\G


——————————————————————————–
上記クエリーは以下のように先頭にAliasを付けてもOK
——————————————————————————–

SELECT COUNT01.* FROM Country as COUNT01
WHERE COUNT01.Continent = 'South America'
AND COUNT01.Population =
(SELECT MIN(COUNT02.Population) FROM Country as COUNT02
WHERE COUNT01.Continent = COUNT02.Continent)\G

——————————————————————–

サンプルその2


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


ALLをサブクエリーに利用する
ALL という語は比較演算子の後に指定するもので、“サブクエリが返すレコードの
ALL（すべて）に対して比較が TRUE の場合 TRUE を返す” ことを表します。
次に例を示します。
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

テーブル t1 に {10} という値を含むレコードがあるとします。 テーブル t2 に含まれている値が {-5,0,+5}
の場合、この式は TRUE になります。なぜなら t2 に含まれている値はすべて 10 より小さいからです。
テーブル t2 に含まれている値が {12,6,NULL,-100} の場合、この式は FALSE になります。
なぜなら t2 に、10 よりも大きい 12 という値が 1 つ存在するからです。 テーブル t2 に含まれている
値が {0,NULL,1} の場合、この式は UNKNOWN になります。

(例)Countryテーブル

以下のサブクエリーは全ての結果からPopulationが “>=”　の値を算出しているので、
必然的にPopulationの値が一番多い”=”大陸が選択されます。
全部の値の中から　”>”　にあたる値は無く “=”　のみが最大値に適用される。

SELECT Continent, Name
FROM Country c1
WHERE Population >= ALL (SELECT Population
FROM Country c2
WHERE c1.Continent=c2.Continent
);


ANYをサブクエリーに利用する
ANY という語は比較演算子の後に指定するもので、“サブクエリが返すレコードの
ANY（いずれか）に対して比較が TRUE の場合 TRUE を返す” ことを表します。 次に例を示します。

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

テーブル t1 に {10} という値を含むレコードがあるとします。 テーブル t2 に含まれている値が
{21,14,7} の場合、この式は TRUE になります。なぜなら t2 に、10 よりも小さい 7 という値が
存在するからです。 テーブル 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);

SOME という語は ANY のエイリアスです。したがって、次の 2 つのステートメントは同じです。

SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

SOME はめったに使用されませんが、上の例では、この語がどのような場合に役立つかを示しています。
“a is not equal to any b” という英語のフレーズを、ほとんどの人は “a と等しい b はまったく存在しない
” という意味に受け取ります。しかし、SQL 構文では意味が異なります。ANY の代わりに <> SOME
を使用することによって、このクエリの本当の意味を誰もが確実に理解できるようにすることができます。

以下のクエリは、面積がAVGより大きい大陸と名前を選択してます。
このサブクエリーは、アウタークエリーとは関係なく実行可能です。（not correlated subquery)


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


上記クエリをALLとANYで比較

INをサブクエリーに利用する


select CountryCode,Language from CountryLanguage
where CountryCode IN(select Code from Country where GovernmentForm = 'monarchy');


EXISTS をサブクエリーに利用する


select A.Code,A.name from Country A
where EXISTS (
select * from CountryLanguage B
where A.Code = B.CountryCode
AND B.Language = 'Japanese');


——————————–
Foot Note
——————————–
Subqueries in the FROM clause of a query cannot be correlated with the outer query.
FROM句のサブクエリーは外部クエリーと依存関係を設定する事が出来ない。

mysql> SELECT Name, Language
-> FROM Country AS c, (SELECT Language
-> FROM CountryLanguage
-> WHERE CountryCode = c.Code
-> ) AS tmp;
ERROR 1054 (42S22): Unknown column ‘c.Code’ in ‘where clause’
mysql>

もし上記クエリーを修正すると以下のような感じでしょうかね。


SELECT C.Name, CL.Language FROM Country AS C,
(SELECT CountryCode,Language FROM CountryLanguage) AS CL
WHERE CL.CountryCode = C.Code;


参考サイト
6.4.2. サブクエリ構文
6.4.2.4. ALL とともに使用したサブクエリ
6.4.2.3. ANY、IN、SOME とともに使用したサブクエリ