問合せの結果をもとに、さらに別の問合せをしたいということもあるでしょう。SQLでは、このような問合せを行うときに、SQL文の中にさらにSQL文を追加して記述できます。これを「副問合せ」や「サブクエリ」と呼びます。
副問い合わせの基本
副問合せは、通常ならば2回問合せる必要があるものを、問合せの中にさらに問合せを入れることで、1回の問合せで実現します。
SELECT ...FROM <表名>
WHERE <列名> <比較演算子>
(SELECT ... FROM <表名> [WHERE <検索条件>]);
表:商品マスタ
商品コード | 商品名 | 販売価格 | 販売元 |
1100 | レンズなしカメラ | 1100 | びっくり愛 |
1300 | 透明なインク | 180 | うつけ屋 |
1201 | 角のない豆腐 | 400 | 裸の大様堂 |
1301 | 開かない蓋 | 480 | 非実用INC |
表:売上リスト
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1300 | 透明なインク | 180 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
1201 | 角のない豆腐 | 400 | 20010602 | 120 |
1301 | 開かない蓋 | 480 | 20010603 | 122 |
SELECT * FROM 売上リスト WHERE 商品コード =
(SELECT 商品コード FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ');
結果
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
SELECT 商品コード FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ'
結果
商品コード |
1100 |
SELECT * FROM 売上リスト WHERE 商品コード = 1100;
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
SELECT 顧客マスタ.名前, 売上リスト.商品名, 売上リスト.販売価格, 売上リスト.販売日
FROM 顧客マスタ INNER JOIN 売上リスト ON 顧客マスタ.顧客コード = 売上リスト.顧客コード
WHERE 売上リスト.商品コード =
(SELECT 商品コード FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ');
ORACLE MS-SQL MSAccess
SELECT 顧客マスタ.名前, 売上リスト.商品名, 売上リスト.販売価格, 売上リスト.販売日
FROM 顧客マスタ, 売上リスト
WHERE 顧客マスタ.顧客コード = 売上リスト.顧客コード AND 売上リスト.商品コード =
(SELECT 商品コード FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ');
結果
名前 | 商品名 | 販売価格 | 販売日 |
斉藤隆 | レンズなしカメラ | 1100 | 20010601 |
佐藤信也 | レンズなしカメラ | 1200 | 20010602 |
エラーとなる副問合せ
副問合せは、通常なら2回の問合せが必要な処理をひとまとめにできる便利な機能です。しかし、副問合せには制限があります。例えば、ORACLEで次の問合せを行うと、結果はエラーが返されます。
SELECT * FROM 売上リスト WHERE 商品コード =
(SELECT 商品コード FROM 商品マスタ WHERE 商品単価>=500);
エラー行:3: エラーが発生しました。
ORA-01427: 単一行副問合せにより2つ以上の行が返されます
SELECT 商品コード FROM 商品マスタ WHERE 商品単価>=500;
結果
商品コード |
1100 |
1301 |
このように、2行の結果を返しているのがわかります。副問合せでは、複数の行が返される問合せを含むSQL文はエラーになります。これは、副問合せから返ってきた結果の、どれを次の問合せに使ってよいか一意に定まらなくなるからです。
この他にも副問合せを含むSQLではエラーとなる場合があります。
SELECT * FROM 売上リスト WHERE 商品コード =
(SELECT * FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ');
エラー行:3: エラーが発生しました。
ORA-00913: 値の個数が多すぎます。
SELECT * FROM 売上リスト WHERE 商品コード =
(SELECT 販売元 FROM 商品マスタ
WHERE 商品名 = 'レンズなしカメラ'
);
エラー行:3: エラーが発生しました。
ORA-01722: 数値が無効です。
相関副問合せ
ここまでの副問合せでは、外側のSELECT文のWHERE句に比較演算子だけを使ってきたので、副問合せから返ってくる値は1つである必要がありました。しかし、相関副問合せ(相関サブクエリ)を用いると、複数の行が返ってくる問合せを副問合せに指定することができます。相関副問合せは、繰り返し副問合せとも呼ばれます。
ここまで紹介してきたものは、副問合せ自体が問合せとして完結していました。これに対して、相関副問合せでは外側の問合せと関連しているため、単独では動作しません。実際の例を見てみましょう。
SELECT * FROM 売上リスト U WHERE 'レンズなしカメラ' =
(SELECT 商品名 FROM 商品マスタ S WHERE S.商品コード = U.商品コード);
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
この入力したSQL文の副問合せをよく見ると、面白いことに気が付きます。副問合せのWHERE句の記述では、外側の問合せから値を受け取っているのです。「U.商品コード」というのは、副問合せにはありません。この「U」は、外側のSQL文で「売上リスト」表の別名に指定されているものです。
副問合せは、外側のSQL文から行の値を受け取り、その受け取った値を処理して外側の問合せに返しているのです。このSQL文の場合には、外側の問合せから副問合せに、「売上リスト」表の全体の行が1行づつ順次渡されています。行を受け取った副問合せは、その中の「商品コード」列に「商品リスト」表の「商品コード」列と同じ値があるかどうかを確認し、同じ値があった行だけを、順次問合せから返ってきた値の中から、「レンズなしカメラ」と一致するものだけを取り出しています。
なお、ここで行った相関副問合せの記述は、次のようなSQL文に書き直すこともできます。
SELECT U.商品コード, U.商品名, U.販売価格, U.販売日, U.顧客コード
FROM 売上リスト U, 商品マスタ S
WHERE S.商品コード = U.商品コード AND S.商品名='レンズなしカメラ';
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
相関副問合せでは、外側の問合せから行を順次うけとって処理をし、合致した値を返していくので、一度に複数行の値が返ることにはなりません。そのため、副問合せのときにあった、複数の行が返るといった点に気を付ける必要はなくなります。
しかし、相関副問合せにも注意点があります。通常の問合せと同様に、複数の列が外側の問合せに返るとエラーとなるので、副問合せの選択列リストは必ず1つでなければなりません。また、副問合せから外側に返される値のデータ型と、外側の問合せのWHERE句で記述されている検索条件のデータ型が異なるとエラーになります。
副問合せからの値の存在を調べる
副問合せからの戻り値を扱うキーワードに、EXISTSキーワードがあります。このキーワードは、副問合せからの戻り値を受け取り、その」値が存在するかどうかを確認し、存在する場合には真を返すものです。
WHERE EXISTS <副問合せ>
SELECT * FROM 売上リスト WHERE EXISTS (
SELECT * FROM 売上リスト WHERE 販売日='20010602');
結果
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1300 | 透明なインク | 180 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
1201 | 角のない豆腐 | 400 | 20010602 | 120 |
1301 | 開かない蓋 | 480 | 20010603 | 122 |
SELECT * FROM 売上リスト WHERE EXISTS (
SELECT * FROM 売上リスト WHERE 販売日='20010604');
結果
レコードが選択されませんでした。
この副問合せでは、「売上リスト」表の「販売日」列に「20010604」が存在するかどうかを確認しています。しかし、「販売日」列にはその値が存在しないため、外側の問合せには値が返りません。するとEXISTSは偽を返すので、結果が存在しなくなってしまっています。
また、相関問合せでも、EXISTSを使うことができます。
SELECT * FROM 売上リスト U WHERE EXISTS
(SELECT * FROM 商品マスタ S
WHERE S.商品名 = 'レンズなしカメラ' AND S.商品コード = U.商品コード );
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
この例では副問合せで商品マスタから商品名が「レンズなしカメラ」かつ「売上リスト」表と「商品マスタ」表の「商品コード」列の値が同じものを取り出しています。これら2つの条件を満たす行が副問合せから外側の問合せに返され、その結果EXISTSが真となります。そのため、結果のように2行が問合せ結果として返されます。
次にEXISTSの否定形であるNOT EXISTSキーワードについても解説しておきます。NOT EXISTSはEXISTSの否定であり、副問合せから値が返されない場合に真になります。正確にいうならば副問合せの行が返ってこないとEXISTSが偽となり、それをNOTによって反転しているので、真になります。
次の例では、副問合せで、商品名が「レンズなしカメラ」以外の行は外側の問合せに値が返されません。その結果NOT EXISTSが真となるため、「レンズなしカメラ」以外の行が返されます。
SELECT * FROM 売上リスト U WHERE NOT EXISTS
(SELECT * FROM 商品マスタ S
WHERE S.商品名 = 'レンズなしカメラ' AND S.商品コード = U.商品コード );
結果
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1300 | 透明なインク | 180 | 20010601 | 112 |
1201 | 角のない豆腐 | 400 | 20010602 | 120 |
1301 | 開かない蓋 | 480 | 20010603 | 122 |
副問合せの条件のいずれかを満たすものを選択する
副問合せで返される結果のいずれかの値が、外側の問合せの条件を満たす場合に真を返すのがANYキーワードです。
<列または値> <比較演算子> ANY <副問合せ>
SELECT * FROM 売上リスト WHERE 販売日 = ANY
(SELECT 販売日 FROM 売上リスト WHERE 商品名 = 'レンズなしカメラ');
結果
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1300 | 透明なインク | 180 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
1201 | 角のない豆腐 | 400 | 20010602 | 120 |
これは、SOMEキーワードを使っても同じ結果を得ることができます。SOMEの書式は、ANYをSOMEに置き換えるだけです。
SELECT * FROM 売上リスト WHERE 販売日 = IN
(SELECT 販売日 FROM 売上リスト WHERE 商品名 = 'レンズなしカメラ');
結果
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1100 | 20010601 | 112 |
1300 | 透明なインク | 180 | 20010601 | 112 |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
1201 | 角のない豆腐 | 400 | 20010602 | 120 |
副問合せの条件すべてを満たすものを選択する
副問合せが返す結果のいずれかの値が、条件を満たす場合に真となるのがALLキーワードになります。
<列または値> <比較演算子> ALL <副問合せ>
ALLは、比較演算子に従って、副問合せの結果を列の値と比較し、すべての値が条件を満たすと真を返します。
SELECT * FROM 売上リスト WHERE 販売日 > ALL
(SELECT 販売日 FROM 売上リスト WHERE 商品名='透明なインク');
実行結果
商品コード | 商品名 | 販売価格 | 販売日 | 顧客コード |
1100 | レンズなしカメラ | 1200 | 20010602 | 115 |
1201 | 角のない豆腐 | 400 | 20010602 | 120 |
1301 | 開かない蓋 | 480 | 20010603 | 122 |
この例では、商品名が「透明なインク」の販売日よりも後に販売された商品の行を取り出しています。