6.サブクエリ

6.1. サブクエリの基本

複数のクエリを組み合わせて、1つのクエリが生成した出力で、他のクエリの出力を制御することができます。少し分かりやすく言うと、クエリを入れ子にして、内側のクエリが値を生成し、それを外側のクエリの述語が評価して TRUE かどうか判断します。

次の図はサブクエリの最も標準的な形です。図では背景がオレンジ色の部分が内側のクエリで、ピンクの部分が外側のクエリということになります。内側のクエリで生成された値を外側のクエリが受け取り、WHERE 句で TRUE となるものについて、値を参照します。そして、内側のクエリのことをサブクエリといいます。

前の図からも予想がつくように、等号や不等号といった関係演算子をベースとする述語に使用するサブクエリは、必ず出力として 1行のみを生成するものでなければなりません。サブクエリが値をまったく生成しない場合、述語は UNKNOWN と評価するが、複数の値を生成する場合、メインクエリ (外側のクエリ) は評価不能に陥ってしまい、ステートメントがエラーを生成するという状況になります。 sample-06-1 は、商品名が 'JUICE' である受注番号を参照するものです。

sample-06-1:

1
2
3
4
5
/* サブクエリ */
SELECT 受注番号 
FROM 受注表
WHERE 商品コード = (SELECT 商品コード FROM 商品表
                   WHERE 商品名 = 'JUICE');

実行結果

●6.2. WHERE 句でのサブクエリ

前節の図でも示したように、基本的なサブクエリは WHERE 句の述語によく用いられます。先ほどの説明で、等号や不等号といった関係演算子をベースとする述語では、サブクエリは 1 行のみを生成するものでなければならないことを示しました。しかし、IN 演算子を使用すれば任意数の行を生成するサブクエリを使用することができるようになります。IN 演算子は含まれる値と含まれない値の集合を明示的に定義するものです。
sample-06-2 は受注した商品の単価が 400 円である受注番号を参照するものです。

sample-06-2:

1
2
3
4
/* サブクエリ */
SELECT 受注番号 FROM 受注表
WHERE 商品コード IN (SELECT 商品コード FROM 商品表
                    WHERE 単価 = 400);

実行結果

6.3. HAVING 句でのサブクエリ

HAVING 句にもサブクエリを使用することが可能です。使用方法は WHERE 句で使用していたのと同じです。
sample-06-3 は受注個数が顧客コード '002' の最小値よりも小さい注文をした顧客コードとその件数を参照するものです。

sample-06-3:

1
2
3
4
5
6
/* サブクエリ */
SELECT 顧客コード,COUNT(DISTINCT 受注番号) 
FROM 受注表
GROUP BY 顧客コード
HAVING 受注個数 < (SELECT MIN(受注個数) FROM 受注表
                  WHERE 顧客コード = '002');

実行結果

●6.4. その他の場所でのサブクエリ

サブクエリは、条件句である WHERE 句や HAVING 句のみだけでなく、SELECT のすぐ後ろに続けて使用することもできます。サブクエリの結果を、メインクエリで直接表示させることができます。たとえば、受注表における全受注個数の平均値と、各顧客の受注個数の平均値を比べて見たい場合は、sample-06-4 のように記述します。

sample-06-4:

1
2
3
4
5
/* サブクエリ */
SELECT 顧客コード,AVG(受注個数),
      (SELECT AVG(受注個数) FROM 受注表)
FROM 受注表
GROUP BY 顧客コード;

実行結果

また、サブクエリは FROM 句の後に続けて使用することもできます。サブクエリの結果セットを一つのテーブルとして、利用するものです。 sample-06-5 は、受注表における各顧客の受注個数の平均の中で、最も大きい値を出力するものです。

sample-06-5:

1
2
3
4
/* サブクエリ */ 
SELECT MAX(AVG_J) AS MAX_AVG
FROM (SELECT AVG(受注個数) AS AVG_J
      FROM 受注表 GROUP BY 顧客コード) ;

実行結果

●6.5. 相関サブクエリ

SQL にサブクエリを使用する場合、内部クエリから外部クエリの FROM 句のテーブルを参照する、相関サブクエリを構成することができます。この場合、メインクエリのテーブルの行ごとに、サブクエリが繰り返し実行されます。 sample-06-6 では納品日が '20010401' の商品を、相関サブクエリを用いて参照します。

sample-06-6:

1
2
3
4
5
/* 相関サブクエリ */
SELECT * FROM 商品表 AA
WHERE '20010401' IN (SELECT 納品日
                     FROM 受注表 BB
                     WHERE AA.商品コード = BB.商品コード);

実行結果

sample-06-6 は少し複雑なので簡単に説明します。 商品表から商品コードが '101' の行を選択する。 この行を、エイリアス AA の候補行として保存する。 次にサブクエリを実行する。サブクエリは、受注表全体から商品コード列が AA.商品コード列 (商品コード '101') と一致する行を検索する。次に受注表からこれが TRUE となる行の納品日列を取り出し、納品日値の結果セットを構築する。 商品コードが '101' である納品日値の値の集合を構築したら、この集合に '20010401' が含まれているかどうかを確認するため、メインクエリの述語を評価する。含まれている場合は (この場合は含まれている)、'101' の行をメインクエリの出力として選択する。 次に、商品コードが '102' の行を候補行として手続き全体を繰り返す。これを商品表の行が 1つ残らず評価されるまで繰り返す。 このような手続きの結果、重複するデータがあっても表示されなくなります。

メインクエリとサブクエリで同じテーブルを使用する相関サブクエリというものもあります。テーブルの自己相関といいますが、複雑な形式の派生情報を抽出することができます。 sample-06-7 では、受注したものの中から受注個数が平均個数を超えるものを検索します。ちなみに、平均個数は 155個です。

sample-06-7:

1
2
3
4
5
/* サブクエリ */
SELECT 受注番号,受注個数 
FROM 受注表 XX
WHERE 受注個数 > (SELECT AVG(受注個数)
                 FROM 受注表 YY);

実行結果

HAVING 句にも相関サブクエリを用いることができます。 sample-06-8 では、受注個数を納品日別に集計し、それぞれの合計が受注個数の最大値を 150 上回るものだけを参照するものです。

sample-06-8:

1
2
3
4
5
6
7
/* サブクエリ */
SELECT SUM(受注個数),納品日
FROM 受注表 PP
GROUP BY 納品日
HAVING SUM(受注個数) > (SELECT 150 + MAX(受注個数)
                       FROM 受注表 QQ
                       WHERE PP.納品日 = QQ.納品日);

実行結果

6.6. EXISTS, ANY, ALL

演算子 EXISTS, ANY, ALL はサブクエリが返す値に対して、「TRUE」または「FALSE」の評価を行い、メインクエリの WHERE 句で使用されます。サブクエリの返す値の集合を結果セットと呼びます。EXISTS 演算子は結果セットの有無を評価します。ANY 演算子は結果セットのいずれかの値を評価対象とします。ALL 演算子は結果セットの全ての値を評価対象とします。

6.6.1.EXISTS

EXIST 演算子はサブクエリの生成した値が存在する場合は「TRUE」、存在しない場合は「FALSE」を返します。ANY 演算子、ALL 演算子は「=」「<」等の比較演算子と共に用いて、全体で「TRUE」または「FALSE」の評価をしますが、EXISTS 演算子だけは単独で「TRUE」または「FALSE」の評価を行います。また、サブクエリの返す値は複数の行のものでも、複数の列のものでも構いません。

sample-06-9:

1
2
3
4
/* EXISTS */
SELECT * FROM 商品表
WHERE EXISTS (SELECT * FROM 商品表
              WHERE 商品名 = 'BEER');

実行結果

sample-06-9 のクエリは、サブクエリで商品表から商品名が 'BEER' と一致するものを出力し、値が出力されれば、商品表の全てを表示するというものです。商品表には商品名が 'BEER' というものがありますので、商品表の全てが参照されます。sample-06-9 のように、EXISTS にはいくつの列が返されても構いません。

相関サブクエリについても EXISTS を使用することが出来ます。 sample-06-10 では、受注表から'JUICE'を受注した受注番号と顧客コードを参照します。相関サブクエリでは、メインクエリから受け取った各行ごとに EXISTS 演算子は「TRUE」か「FALSE」の評価を行います。

sample-06-10:

1
2
3
4
5
/* EXISTS */
SELECT 受注番号,顧客コード  
FROM 受注表 JJ
WHERE EXISTS (SELECT * FROM 商品表 SS
              WHERE 商品名 = 'JUICE' AND JJ.商品コード = SS.商品コード);

実行結果

sample-06-10 では、まず受注表から受注番号が '10001' である行をサブクエリで使用します。この行の商品コードを商品表の商品コードと比較します。また同時にその商品名が 'JUICE' であるかもチェックしています。この行の商品コードは '102' で、同時に商品表の商品名は 'JUICE' となります。この行と商品表の各行の比較が終わったとき、サブクエリは商品表の商品名が 'JUICE' の行について、全ての列の値を出力します。EXISTS 演算子はこれを受け取って「TRUE」と評価します。そして受注番号が '10001' の行は出力されることが決まり、受注番号と顧客コードが表示されるということになります。そして同じことが受注表の全ての行に対して行われ、結局 'JUICE' を注文したときの行についてのみ出力されることにな ります。

通常のサブクエリでは、EXISTS 演算子は結果の存在有無を一度しか評価しませんが、相関サブクエリではそれぞれの行に対して評価を行います。

EXISTS 演算子には、NOT EXISTS と記述することで EXISTS 演算子と反対の役割を果たすようになります。すなわち、サブクエリの値が存在するときは「FALSE」、存在しないときは「TRUE」と評価します。

6.6.2. ANY

注：SQLiteで実行することができません
ANY 演算子はサブクエリの生成した値のいずれかの値を評価対象とします。ANY 演算子は比較演算子と共に用いられ、結果セットの値のいずれかが比較演算子の関係を満たす場合に「TRUE」、満たさなければ「FALSE」を返します。また、サブクエリが生成する値は複数でも構いません。

次の図はメインクエリの WHERE 句の述語を表しています。サブクエリから生成された値の集合である結果セット [value A, value B, value C, ...] でいずれかが、[列名] の値と比較して比較演算子との関係を満たせば、「TRUE」を返します。満たさない場合はもちろん「FALSE」を返します。

([列名] + 比較演算子 + value A) OR ([列名] + 比較演算子 + value B) OR ([列名] + 比較演算子 + value C) ... というようなイメージです。

sample-06-11 では、受注表において納品日が顧客コードが '002' のものと同じであるものを参照します。

sample-06-11:

1
2
3
4
5
/* ANY */
SELECT 顧客コード,納品日 
FROM 受注表
WHERE 納品日 = ANY (SELECT 納品日 FROM 受注表
                  WHERE 顧客コード = '002');

サブクエリでは、顧客コードが '002' の納品日を出力します。納品日が [20010401, 20010402] という結果セットが作られます。この結果セットの中のいずれかについてメインクエリの WHERE 句で「TRUE」になるものについて、顧客コードと納品日が出力されます。これは、前に説明した IN を用いた場合と同じ効果が得られます。 sample-06-12 は IN を使用して、 sample-06-11 と同じ出力を得るものです。

sample-06-12:

1
2
3
4
5
/* IN */
SELECT 顧客コード,納品日 
FROM 受注表
WHERE 納品日 IN (SELECT 納品日 FROM 受注表
                WHERE 顧客コード = '002');

実行結果

しかし、ANY 演算子は IN 演算子では出来なかったことが出来るようになっています。ANY 演算子は比較演算子として、「=」だけでなく「>」「<=」などの不等号も使用できるのです。 sample-06-13 では受注表において、受注個数が顧客コード '004' の受注個数の最大よりも小さなものについて、参照します。

sample-06-13:

1
2
3
4
5
/* ANY */
SELECT 受注番号,顧客コード,受注個数 
FROM 受注表
WHERE 受注個数 < ANY (SELECT 受注個数 FROM 受注表
                     WHERE 顧客コード = '004');

また ANY 演算子と全く同じ機能を持つ演算子があります。SOME 演算子を用いることにより、ANY 演算子と同じ効果が得られます。

6.6.3. ALL

注：SQLiteで実行することができません
ALL 演算子はサブクエリの生成した値の全てを評価対象とします。ANY 演算子と同じように、ALL 演算子も比較演算子と共に用いられます。結果セットの値の全てが比較演算子との関係を満たす場合に「TRUE」、満たさなければ「FALSE」を返します。また、ANY 演算子と同様に、サブクエリが生成する値は複数でも構いません。

先ほどの図とほぼ変わりありませんが、次の図で言うと、結果セット [value A, value B, value C, ...] の全てが、[列名] の値と比較して比較演算子との関係を満す時「TRUE」を返します。満たさない場合は「FALSE」を返します。

([列名] + 比較演算子 + value A) AND ([列名] + 比較演算子 + value B) AND ([列名] + 比較演算子 + value C) ... というようなイメージです。

sample-06-14 は、 sample-06-13 の ANY を ALL に変えただけです。受注表から受注個数が顧客コード '004' の受注個数の最小値より小さなものについて、受注番号、顧客コード及び受注個数を参照します。

sample-06-14:

1
2
3
4
/* ALL */
SELECT 受注番号,顧客コード,受注個数 FROM 受注表
WHERE 受注個数 < ALL (SELECT 受注個数 FROM 受注表
                    WHERE 顧客コード = '004');

6.7 SQL文

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SELECT 受注番号 FROM 受注表
WHERE 商品コード =(SELECT 商品コード FROM 商品表 
                  WHERE 商品名 = 'JUICE');

SELECT 受注番号 FROM 受注表
WHERE 商品コード IN (SELECT 商品コード FROM 商品表
                    WHERE 単価 = 400);

SELECT 顧客コード,COUNT(DISTINCT 受注番号) 
FROM 受注表
GROUP BY 顧客コード
HAVING 受注個数 < (SELECT MIN(受注個数) FROM 受注表
                  WHERE 顧客コード = '002');   

SELECT 顧客コード,AVG(受注個数),
      (SELECT AVG(受注個数) FROM 受注表)
FROM 受注表
GROUP BY 顧客コード;  

SELECT MAX(AVG_J) AS MAX_AVG
FROM (SELECT AVG(受注個数) AS AVG_J
      FROM 受注表 GROUP BY 顧客コード) ;

SELECT * FROM 商品表 AA
WHERE '20010401' IN (SELECT 納品日
                     FROM 受注表 BB
                     WHERE AA.商品コード = BB.商品コード);


SELECT 受注番号,受注個数
FROM 受注表 XX
WHERE 受注個数 >  (SELECT AVG(受注個数)
                  FROM 受注表 YY);

SELECT SUM(受注個数),納品日
FROM 受注表 PP
GROUP BY 納品日
HAVING SUM(受注個数) > (SELECT 150 + MAX(受注個数)
                       FROM 受注表 QQ
                       WHERE PP.納品日 = QQ.納品日);

SELECT * FROM 商品表
WHERE EXISTS (SELECT * FROM 商品表
              WHERE 商品名 = 'BEER');

SELECT 受注番号,顧客コード
FROM 受注表 JJ
WHERE EXISTS (SELECT * FROM 商品表 SS
              WHERE 商品名 = 'JUICE' AND JJ.商品コード = SS.商品コード);

SELECT 顧客コード,納品日
FROM 受注表
WHERE 納品日 IN (SELECT 納品日 FROM 受注表
                WHERE 顧客コード = '002');