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

※ ともにそれぞれの大陸で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)


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

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;

subquery_sample

以下の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';

subquery_correlation

上記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

——————————————————————–
subquery_change_relation

    サンプルその2


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

select_sub_query

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テーブル
all_t1

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

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

all

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
);

any

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

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


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

in_select

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


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

exsists

——————————–
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>

from_and_subquery

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


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

subquery_fix

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


GROUP_CONCAT、サブクエリーを利用してデータの抽出を行い
CONCATを用いて文字列を連結する。

CONCAT(str1,str2,…)

引数を連結した結果であるストリングを戻します。ひとつ以上の引数を持つ場合があります。
すべての引数が非バイナリ ストリングである場合、結果は非バイナリ ストリングになります。
引数がひとつでもバイナリ ストリングを含む場合は、結果はバイナリ ストリングになります。
数値の引数はそれに等しいバイナリ ストリング形態に変換されます。それを避けたい場合は、
次の例のように、明示的なタイプ キャストを使用することができます :

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

サンプルテーブル
sample

サンプルデータ
sample_data

上記のテーブルからオフィシャル言語が多い国を抽出してみる。


select B.NAME,Count(*) SUM from
CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
Order by SUM desc
limit 1;

select_official

select B.NAME,Count(*) SUM,
GROUP_CONCAT(Language) LANG
from CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
ORDER BY SUM DESC
limit 1;

gc1


SELECT TMP.NAME,TMP.SUM,TMP.LANG FROM
(
select B.NAME,Count(*) SUM,
GROUP_CONCAT(Language) LANG
from CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
ORDER BY SUM DESC
limit 1
) AS TMP;

gc2

上記結果をCONCATで結合して文言にする。

SELECT
CONCAT(
'The country ',
(SELECT Name FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp
),
' has ',
(SELECT nlanguages FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp1
),
' official languages: ',
(SELECT languages FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp2
)
) AS '文字の結合';

concat_sentenct

参考サイト
11.11.1. GROUP BY (Aggregate) Functions


11.3. 文字列関数


「サブクエリー+IN」、「LEFT JOIN」、「RIGHT JOIN」により
テーブルから同じ結果を抽出。

General Query (IN)

SELECT * FROM project WHERE project.id IN
(SELECT client.id FROM client)

LEFT JOIN

select B.PID,B.ID,B.NAME,B.START,B.END from client A
LEFT JOIN project B using(ID) /* or: ON A.ID = B.ID */
WHERE B.NAME IS NOT NULL;

RIGHT JOIN

select B.PID,B.ID,B.NAME,B.START,B.END from project B
RIGHT JOIN client A using(ID) /* or: ON A.ID = B.ID */
WHERE B.NAME IS NOT NULL;

right_join


サブクエリーのSELECTステートメントを利用して次のテーブルから,Western Europeの中で
ドイツ語を母国語とする人口を算出するクエリーサンプル。

テーブルサンプル
subquery_select

Country には人口
CountryLanguageには言語毎の割合が%で入っている


select sum(tmp.German_Speakers) from
(
select (A.Population * B.Percentage) / 100 as 'German_Speakers'
from Country A, CountryLanguage B
where A.Region = 'Western Europe'
and A.Code = B.CountryCode
and B.Language = 'German'
) as tmp;

population

参考サイト

12.2.9.1. The Subquery as Scalar Operand


12.2.8.5. 行サブクエリ

この時点までの話は、単一値や値のカラムを返すサブクエリなどのような、
スカラやカラム サブクエリに関しての物でした。行サブクエリ は単列を戻し、
ひいては複数のカラム値を返す事ができるサブクエリ異型です。ここに2つ例があります。

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

もしテーブル t2 が column1 = 1 と column2 = 2 の場所に行を持っていれば、
ここにあるクエリは両方 TRUE です。

select_pop

Houstonの人口をシンプルなクエリーと行コンストラクタを利用して抽出してみました。
■シンプル
select Population from City where Name = ‘Houston’;
■行コンストラクタ
select Population from City where (Name,District,CountryCode) = (‘Houston’,’Texas’,’USA’);

row_contructor

参考サイト

12.2.8.5. 行サブクエリ


EXISTS と NOT EXISTS

もしサブクエリが行を返せば、
EXISTS subquery は TRUE で、
NOT EXISTS subquery は FALSE です。

もともと、EXISTS サブクエリは SELECT * で開始しますが、
SELECT 5 や SELECT column1、またそれ以外のどんな物でも開始する事ができます。
MySQL はそのようなサブクエリの中では SELECT リストを無視するので、何も変わらないのです。


select distinct Language from CountryLanguage
where CountryCode IN(
select Code from Country where
GovernmentForm = 'Monarchy')
order by Language;

select Language from CountryLanguage
where CountryCode IN(
select Code from Country where
GovernmentForm = 'Monarchy')
group by Language
order by Language;

select_sub

以下のINを使ったサブクエリーとEXISTSを利用したサブクエリーは同じ値を返す。


select Name from Country where Code IN
(select CountryCode from CountryLanguage
where Language = 'German')
Order by Name;

in


select Name from Country where EXISTS
(
select * from CountryLanguage
where Code = CountryCode
AND Language = 'German'
)
Order by Name;

exists


select Name from Country where EXISTS
(
select CountryCode from CountryLanguage
where Code = CountryCode
AND Language = 'German'
)
Order by Name;

code_5

参考サイト
12.2.8.6. EXISTS と NOT EXISTS


構文

ALL ()

ALL という語は比較演算子の後に指定するもので、“サブクエリが返すレコードの
ALL(すべて)に対して比較が TRUE の場合 TRUE を返す” ことを表します。

(例)
このQueryは、同じ大陸に存在する国で一番人口の多い国を結果として返す。


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

ALLをつけないと複数列が返るのでエラーになる。
sub_all

参考サイト
6.4.2.4. ALL とともに使用したサブクエリ

6.4.2. サブクエリ構文

サブクエリ (この記事は4.1版の新しい機能を紹介する6番目の記事)