【SQLの基礎知識⑮】 副問い合わせ(サブクエリ)

SQLの基礎知識
SQLの基礎知識
この記事は約14分で読めます。

問合せの結果をもとに、さらに別の問合せをしたいということもあるでしょう。SQLでは、このような問合せを行うときに、SQL文の中にさらにSQL文を追加して記述できます。これを「副問合せ」や「サブクエリ」と呼びます。

Recommended Articles

副問い合わせの基本

副問合せは、通常ならば2回問合せる必要があるものを、問合せの中にさらに問合せを入れることで、1回の問合せで実現します。

書式
SELECT ...FROM <表名>
WHERE <列名> <比較演算子>
(SELECT ... FROM <表名> [WHERE <検索条件>]);
この書式のように、副問合せは「( )」で囲むことにより、外側のSQLと分離を行っています。
まずは「商品マスタ」表と「売上リスト」表を使い、商品名から、売上リストを取り出してみます。

表:商品マスタ

商品コード商品名販売価格販売元
1100レンズなしカメラ1100びっくり愛
1300透明なインク180うつけ屋
1201角のない豆腐400裸の大様堂
1301開かない蓋480非実用INC

表:売上リスト

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ110020010601112
1300透明なインク18020010601112
1100レンズなしカメラ120020010602115
1201角のない豆腐40020010602120
1301開かない蓋48020010603122
入力
SELECT * FROM 売上リスト WHERE 商品コード =
(SELECT 商品コード FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ');

結果

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ110020010601112
1100レンズなしカメラ120020010602115
このように、「レンズなしカメラ」の売上リストを取得することができました。副問合せはSQLの組合せになっているので、このSQL文を個別にみてみましょう。まず副問合せの部分のSQLを単独で実行します。
入力
SELECT 商品コード FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ'

結果

商品コード
1100
このように、「レンズなしカメラ」の商品コードを取得することができました。ここで返された結果をもとに、副問合せの外側にあるSQLを実行してみます。副問合せの部分が上記のコードで返された値になるので、次のようなSQL文になります。
入力
SELECT * FROM 売上リスト WHERE 商品コード = 1100;
結果

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ110020010601112
1100レンズなしカメラ120020010602115
このように返される結果は、副問合せを含んだSQL文と同じになります。
また、副問合せを使って結合を行うこともできます。例えば、次のSQL文では、副問合せで商品コードを取得し、そこで得た商品コードをもとに、「顧客マスタ」表と「売上リスト」表を内部結合したものから売上リストを取得しています。
入力  MS-SQL  MSAccess
SELECT 顧客マスタ.名前, 売上リスト.商品名, 売上リスト.販売価格, 売上リスト.販売日
FROM 顧客マスタ INNER JOIN 売上リスト ON 顧客マスタ.顧客コード = 売上リスト.顧客コード
WHERE 売上リスト.商品コード =
(SELECT 商品コード FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ');
入力
ORACLE
 MS-SQL MSAccess
SELECT 顧客マスタ.名前, 売上リスト.商品名, 売上リスト.販売価格, 売上リスト.販売日
FROM 顧客マスタ, 売上リスト
WHERE 顧客マスタ.顧客コード = 売上リスト.顧客コード AND 売上リスト.商品コード =
(SELECT 商品コード FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ');

結果

名前商品名販売価格販売日
斉藤隆レンズなしカメラ110020010601
佐藤信也レンズなしカメラ120020010602
上記のように、内部結合を指定することで、「名前」列を含んだ売上リストを作成することができます。

エラーとなる副問合せ

副問合せは、通常なら2回の問合せが必要な処理をひとまとめにできる便利な機能です。しかし、副問合せには制限があります。例えば、ORACLEで次の問合せを行うと、結果はエラーが返されます。

入力
SELECT * FROM 売上リスト WHERE 商品コード =
(SELECT 商品コード FROM 商品マスタ WHERE 商品単価>=500
);
結果
エラー行:3: エラーが発生しました。
ORA-01427: 単一行副問合せにより2つ以上の行が返されます
副問合せの結果が、2行以上返されたというエラーです。そこで、副問合せの部分だけを実行してみます。
入力
SELECT 商品コード FROM 商品マスタ WHERE 商品単価>=500;

結果

商品コード
1100
1301

このように、2行の結果を返しているのがわかります。副問合せでは、複数の行が返される問合せを含むSQL文はエラーになります。これは、副問合せから返ってきた結果の、どれを次の問合せに使ってよいか一意に定まらなくなるからです。

この他にも副問合せを含むSQLではエラーとなる場合があります。

入力
SELECT * FROM 売上リスト WHERE 商品コード =
(SELECT * FROM 商品マスタ WHERE 商品名 = 'レンズなしカメラ');
結果
エラー行:3: エラーが発生しました。
ORA-00913: 値の個数が多すぎます。
上記のSQLでは副問合せの列の指定が1つの列でないため、返ってくる結果が1行であっても、複数の列が返ってきます。したがって、返ってきた値のどれと商品コードをを比較すればよいかわからないのでエラーになります。
もう1つエラーとなる例として、次のような副問合せもあります。
入力
SELECT * FROM 売上リスト WHERE 商品コード =
(SELECT 販売元 FROM 商品マスタ
WHERE 商品名 = 'レンズなしカメラ'
);
結果
エラー行:3: エラーが発生しました。
ORA-01722: 数値が無効です。
これは副問合せで返ってくる値と、本体の問合せのWHERE句の条件式で期待されている値が異なっているために、エラーとなっています。この例では、副問合せで返ってくる値が「販売元」列であるので、本体のWHERE句で比較している「商品コード」列とは比較できないのです。

相関副問合せ

ここまでの副問合せでは、外側のSELECT文のWHERE句に比較演算子だけを使ってきたので、副問合せから返ってくる値は1つである必要がありました。しかし、相関副問合せ(相関サブクエリ)を用いると、複数の行が返ってくる問合せを副問合せに指定することができます。相関副問合せは、繰り返し副問合せとも呼ばれます。

ここまで紹介してきたものは、副問合せ自体が問合せとして完結していました。これに対して、相関副問合せでは外側の問合せと関連しているため、単独では動作しません。実際の例を見てみましょう。

入力
SELECT * FROM 売上リスト U WHERE 'レンズなしカメラ' =
(SELECT 商品名 FROM 商品マスタ S WHERE S.商品コード = U.商品コード);
結果

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ110020010601112
1100レンズなしカメラ120020010602115

この入力したSQL文の副問合せをよく見ると、面白いことに気が付きます。副問合せのWHERE句の記述では、外側の問合せから値を受け取っているのです。「U.商品コード」というのは、副問合せにはありません。この「U」は、外側のSQL文で「売上リスト」表の別名に指定されているものです。

副問合せは、外側のSQL文から行の値を受け取り、その受け取った値を処理して外側の問合せに返しているのです。このSQL文の場合には、外側の問合せから副問合せに、「売上リスト」表の全体の行が1行づつ順次渡されています。行を受け取った副問合せは、その中の「商品コード」列に「商品リスト」表の「商品コード」列と同じ値があるかどうかを確認し、同じ値があった行だけを、順次問合せから返ってきた値の中から、「レンズなしカメラ」と一致するものだけを取り出しています。

なお、ここで行った相関副問合せの記述は、次のようなSQL文に書き直すこともできます。

入力
SELECT U.商品コード, U.商品名, U.販売価格, U.販売日, U.顧客コード
FROM 売上リスト U, 商品マスタ S
WHERE S.商品コード = U.商品コード
 AND S.商品名='レンズなしカメラ';
結果

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ110020010601112
1100レンズなしカメラ120020010602115

相関副問合せでは、外側の問合せから行を順次うけとって処理をし、合致した値を返していくので、一度に複数行の値が返ることにはなりません。そのため、副問合せのときにあった、複数の行が返るといった点に気を付ける必要はなくなります。

しかし、相関副問合せにも注意点があります。通常の問合せと同様に、複数の列が外側の問合せに返るとエラーとなるので、副問合せの選択列リストは必ず1つでなければなりません。また、副問合せから外側に返される値のデータ型と、外側の問合せのWHERE句で記述されている検索条件のデータ型が異なるとエラーになります。

副問合せからの値の存在を調べる

副問合せからの戻り値を扱うキーワードに、EXISTSキーワードがあります。このキーワードは、副問合せからの戻り値を受け取り、その」値が存在するかどうかを確認し、存在する場合には真を返すものです。

書式
WHERE EXISTS <副問合せ>
入力
SELECT * FROM 売上リスト WHERE EXISTS (
SELECT * FROM 売上リスト WHERE 販売日='20010602');

結果

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ110020010601112
1300透明なインク18020010601112
1100レンズなしカメラ120020010602115
1201角のない豆腐40020010602120
1301開かない蓋48020010603122
この例の副問合せでは、「売上リスト」表の「販売日」列に「20010602」が存在するかどうかを調べています。「販売日」列には「20010602」が存在しているので必ず値を外側に返し、EXISTSキーワードで真となるので、全行が出力されることになります
入力
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レンズなしカメラ110020010601112
1100レンズなしカメラ120020010602115

この例では副問合せで商品マスタから商品名が「レンズなしカメラ」かつ「売上リスト」表と「商品マスタ」表の「商品コード」列の値が同じものを取り出しています。これら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透明なインク18020010601112
1201角のない豆腐40020010602120
1301開かない蓋48020010603122

副問合せの条件のいずれかを満たすものを選択する

副問合せで返される結果のいずれかの値が、外側の問合せの条件を満たす場合に真を返すのがANYキーワードです。

書式
<列または値> <比較演算子> ANY
<副問合せ>
ANYは、比較演算子に従ってそれぞれの値を列の値と比較し、いずれかの値が条件を満たすと真を返します。
入力
SELECT * FROM 売上リスト WHERE 販売日 = ANY
(SELECT 販売日 FROM 売上リスト WHERE 商品名 = 'レンズなしカメラ'); 

結果

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ110020010601112
1300透明なインク18020010601112
1100レンズなしカメラ120020010602115
1201角のない豆腐40020010602120
この例の場合は、副問合せで「レンズなしカメラ」の販売日である、「20010601」と「20010602」を返しています。この販売日を受け取った外側の問合せでは、販売日が同じ行をすべて取り出しています。
これは、SOMEキーワードを使っても同じ結果を得ることができます。SOMEの書式は、ANYをSOMEに置き換えるだけです。
またANY、SOMEと同様の結果を、INキーワードを使って実現することもできます。INを使う場合には、次のように記述します。
入力
SELECT * FROM 売上リスト WHERE 販売日 = IN
(SELECT 販売日 FROM 売上リスト WHERE 商品名 = 'レンズなしカメラ'); 

結果

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ110020010601112
1300透明なインク18020010601112
1100レンズなしカメラ120020010602115
1201角のない豆腐40020010602120
ただし、ANY、SOMEではさまざまな比較演算子が使えるため、「~以上」、「~以下」といった条件も指定できますが、INでは等しいものしか取り出すことができない点に注意してください。

副問合せの条件すべてを満たすものを選択する

副問合せが返す結果のいずれかの値が、条件を満たす場合に真となるのがALLキーワードになります。

書式
<列または値> <比較演算子> ALL <副問合せ>

ALLは、比較演算子に従って、副問合せの結果を列の値と比較し、すべての値が条件を満たすと真を返します。

SQL文
SELECT * FROM 売上リスト WHERE 販売日 > ALL
(
SELECT 販売日 FROM 売上リスト WHERE 商品名='透明なインク');

実行結果

商品コード商品名販売価格販売日顧客コード
1100レンズなしカメラ120020010602115
1201角のない豆腐40020010602120
1301開かない蓋48020010603122

この例では、商品名が「透明なインク」の販売日よりも後に販売された商品の行を取り出しています。

関連リンク

次の章へ進む
【SQLの基礎知識⑯】 データの操作(insert・update・delete)
この章では、INSERT文、UPDATE文、DELETE文の基礎を習得できます。いちれべ.comは、オラクル、MS-SQL、MS-Access で使用可能か一目でわかるサイトです。
目次
【SQLの基礎知識】基本ルールや基礎の基礎をマスターしよう
こちらの記事を読み進めることで、SQLの基礎が充分に身に付きます。これからSQLを身に着けたい初心者・入門者の方に最適です。データベース製品ごとに表現方法が異なることがありますので、ある程度SQLのスキルがある方も利用できるサイトです。
Recommended Articles
いちれべ.com