SQLの副問い合わせ(サブクエリ)の読み方|IN・EXISTSを例題で理解
WHEREの中にもう1つSELECTが出てくると、どこから読めばいいのか分からなくなる。
カッコの中にSELECT文が入ったSQLを見て、読むのをあきらめたことはありませんか?
先に答えをお伝えします。副問い合わせ(サブクエリ)は、内側のSELECTから先に読むのが基本です。内側が返すものが1つの値なのか、いくつかの値の集まりなのかを書き出し、それを外側の条件に当てはめれば結果が分かります。
EXISTSを使う形だけは少し読み方が違い、外側の行を1行ずつ取り出して、内側に当てはまる行があるかを確かめます。この記事では、この記事のために作った2つの小さな表を使い、IN・EXISTS・比較演算子の3つの形を順番に読んでいきます。
基本情報技術者試験の全体像を先に確かめたい人は、こちらの親記事が参考になります。
【関連記事】基本情報技術者試験とは?試験内容・難易度・勉強法を初心者向けに解説
副問い合わせ(サブクエリ)とは?SQLの中に入れたもう1つのSELECT¶
副問い合わせとは、SQL文の中にカッコで包んで書いた、もう1つのSELECT文のことです。外側のSQLを主問い合わせ、カッコの中を副問い合わせと呼び分けます。
たとえば「平均より高い商品を探したい」とき、平均の値は先に計算しないと分かりません。副問い合わせを使うと、その計算と絞り込みを1つのSQLにまとめて書けます。
この記事で使う2つの表¶
ここからは、次の商品表と注文表を使います。注文表の商品コードを見れば、どの商品が注文されたのかが分かる関係です。
まず商品表です。4つの商品と、それぞれの価格が入っています。
| 商品コード | 商品名 | 価格 |
|---|---|---|
| P1 | ノート | 150 |
| P2 | ボールペン | 120 |
| P3 | 消しゴム | 80 |
| P4 | ファイル | 200 |
次に注文表です。ノートは2回注文されていて、ボールペンとファイルは一度も注文されていません。
| 注文番号 | 商品コード | 数量 |
|---|---|---|
| 1 | P1 | 3 |
| 2 | P3 | 1 |
| 3 | P1 | 2 |
同じ商品が複数回出てくる行と、一度も出てこない商品を、わざと両方入れてあります。副問い合わせと結合の違いは、こうした行の扱いに表れるからです。
副問い合わせを読む3つの手順¶
副問い合わせは、次の3つの手順で読むと迷いにくくなります。まずは手順を表で確かめておきましょう。
| 手順 | やること | 書き出すもの |
|---|---|---|
| 1 | カッコの中のSELECTだけを取り出して実行する | 内側の結果の表 |
| 2 | 返ってきたものが1つの値か、値の集まりかを見分ける | 値、または値の一覧 |
| 3 | 外側のWHEREに当てはめて、1行ずつ判定する | 残る行 |
ポイントは、手順2で内側の結果を紙に書き出すことです。頭の中だけで考えると、外側と内側の条件が混ざりやすくなります。
SELECT・WHERE の基本の読み方に不安がある人は、先にこちらの記事で確かめておくと読みやすくなります。
【関連記事】基本情報のSQL問題の解き方|SELECT・WHERE・ORDER BYを例題で解説
1つの値を返す副問い合わせ|比較演算子と組み合わせる¶
いちばん読みやすいのは、内側が1つの値だけを返す形です。外側では、その値を = や > などの比較演算子で使います。比較演算子とは、2つの値の大小や等しさを調べる記号のことです。
平均より高い商品を探す¶
次のSQLは、価格が全商品の平均より高い商品の名前を探します。
SELECT 商品名
FROM 商品
WHERE 価格 > (SELECT AVG(価格) FROM 商品)
手順1として、カッコの中だけを実行します。AVGは平均を求める関数なので、4つの価格を足して4で割ります。
150 + 120 + 80 + 200 = 550 なので、550 ÷ 4 = 137.5 です。内側の結果は、137.5という1つの値になります。
手順3として、外側のWHEREを 価格 > 137.5 と読み替え、商品表を1行ずつ判定します。
| 商品名 | 価格 | 価格 > 137.5 | 結果に残るか |
|---|---|---|---|
| ノート | 150 | 成り立つ | 残る |
| ボールペン | 120 | 成り立たない | 残らない |
| 消しゴム | 80 | 成り立たない | 残らない |
| ファイル | 200 | 成り立つ | 残る |
結果はノートとファイルの2行です。内側を先に1つの値へ置き換えてしまえば、ふつうのWHEREと同じように読めます。
1つの値を返さないとエラーになる¶
比較演算子と組み合わせる副問い合わせは、必ず1つの値を返す必要があります。もし内側が2行以上を返すと、どの値と比べればよいのか決まらず、SQLはエラーになります。
たとえば 価格 = (SELECT 価格 FROM 商品 WHERE 価格 > 100) と書くと、内側は150・120・200の3つを返します。このような、値がいくつか返る副問い合わせを外側で使うときは、次に説明するINを使います。
INを使う副問い合わせ|内側が返す集合に含まれるかを調べる¶
INは、左側の値が、右側に並んだ値の集まりのどれかと等しいかを調べる書き方です。WHERE 商品コード IN ('P1', 'P3') と書けば、商品コードがP1かP3の行が残ります。
この右側の一覧を、副問い合わせで作ることができます。内側が返す値の集まりを、ここでは集合と呼びます。
注文されたことのある商品を探す¶
次のSQLは、一度でも注文されたことのある商品の名前を探します。
SELECT 商品名
FROM 商品
WHERE 商品コード IN (SELECT 商品コード FROM 注文)
手順1として、内側の SELECT 商品コード FROM 注文 だけを実行します。注文表の商品コードを上から並べると、P1・P3・P1 の3行です。
手順2で、これを集合として書き出します。INで使うときは同じ値が何度あっても意味は変わらないので、P1とP3の2つと考えて構いません。
手順3として、外側を 商品コード IN ('P1', 'P3') と読み替えます。
| 商品名 | 商品コード | P1かP3か | 結果に残るか |
|---|---|---|---|
| ノート | P1 | 含まれる | 残る |
| ボールペン | P2 | 含まれない | 残らない |
| 消しゴム | P3 | 含まれる | 残る |
| ファイル | P4 | 含まれない | 残らない |
結果はノートと消しゴムの2行です。ノートは2回注文されていますが、結果には1回しか出てきません。
結合(JOIN)と結果の行数が違う¶
同じことを結合で書くと、結果が変わる点に注意が必要です。商品表と注文表を商品コードで内部結合すると、ノートには相手が2行あるので、ノートの行が2回出てきます。
副問い合わせのINは、外側の商品表の1行ごとに、含まれるか含まれないかを判定するだけです。そのため、外側の行が増えることはありません。
| 書き方 | 結果の行 | 行数 |
|---|---|---|
| IN を使う副問い合わせ | ノート、消しゴム | 2行 |
| 商品と注文の内部結合 | ノート、消しゴム、ノート | 3行 |
結合で相手が複数見つかると行が増える仕組みは、こちらの記事で図を使って解説しています。
【関連記事】基本情報のSQL結合(JOIN)がわからない!内部結合・外部結合を図解
NOT IN は含まれない行を残す¶
INの前にNOTを付けると、集合に含まれない行が残ります。WHERE 商品コード NOT IN (SELECT 商品コード FROM 注文) なら、一度も注文されていないボールペンとファイルが結果になります。
ただし、内側の結果にNULLが1つでも混ざっていると、NOT INはどの行も残さなくなります。NULLとは、値が入っていないことを表す特別な印です。NULLとの比較は正しいとも間違いとも決まらないため、含まれないと言い切れなくなるのが理由です。
この仕組みは、同じ日に公開したこちらの記事で詳しく説明しています。
【関連記事】SQLのNULLは0や空文字と何が違う?IS NULLと三値論理を例題で解説
EXISTSを使う副問い合わせ|外側の1行ずつ内側を調べる¶
EXISTSは、カッコの中のSELECTが1行でも結果を返せば真、1行も返さなければ偽になる書き方です。真とは条件が成り立つこと、偽とは成り立たないことを指します。
EXISTSの副問い合わせは、多くの場合、内側の条件で外側の表の列を使います。このような副問い合わせを、相関副問い合わせと呼びます。
相関副問い合わせは外側の行ごとに実行する¶
次のSQLも、一度でも注文されたことのある商品を探しています。
SELECT 商品名
FROM 商品
WHERE EXISTS (SELECT *
FROM 注文
WHERE 注文.商品コード = 商品.商品コード)
内側のWHEREに、外側の表の列である 商品.商品コード が出てきます。この値は外側で今どの行を見ているかで変わるので、内側だけを取り出して先に実行することはできません。
そこで読み方を変えます。外側の商品表から1行ずつ取り出し、その行の商品コードを内側に入れて、注文表に当てはまる行があるかを確かめます。
| 外側の行 | 内側の条件 | 注文表で当てはまる行 | EXISTS | 結果に残るか |
|---|---|---|---|---|
| ノート | 注文.商品コード = 'P1' | 注文1、注文3 | 真 | 残る |
| ボールペン | 注文.商品コード = 'P2' | なし | 偽 | 残らない |
| 消しゴム | 注文.商品コード = 'P3' | 注文2 | 真 | 残る |
| ファイル | 注文.商品コード = 'P4' | なし | 偽 | 残らない |
結果はノートと消しゴムで、INを使ったときと同じ2行になります。EXISTSは行があるかないかだけを見るので、内側を SELECT * と書いても、どの列を選んでも結果は変わりません。
NOT EXISTS は相手がいない行を残す¶
EXISTSの前にNOTを付けると、内側が1行も返さなかった行が残ります。上のSQLをNOT EXISTSに変えれば、結果はボールペンとファイルの2行です。
NOT EXISTSは、内側にNULLが混ざっていても、行があるかないかで判定します。そのため、NOT INで起きる、どの行も残らなくなる問題が起きません。
IN・EXISTS・比較演算子の違い¶
ここまでの3つの形を、読み方の違いで比べておきましょう。
| 形 | 内側が返すもの | 読む順番 | 外側の行が増えるか |
|---|---|---|---|
| 比較演算子(= や >) | 1つの値 | 内側を先に実行する | 増えない |
| IN / NOT IN | 値の集合 | 内側を先に実行する | 増えない |
| EXISTS / NOT EXISTS | 行があるかないか | 外側を1行ずつ、内側を毎回調べる | 増えない |
内側に外側の列が出てくるかどうかで、読む順番を決めると整理しやすくなります。出てこなければ内側から、出てくれば外側の1行ずつから読みます。
副問い合わせの練習問題¶
ここまでの内容を使って、結果を予測する練習をしてみましょう。答えを見る前に、内側の結果を紙に書き出してから考えてみてください。
練習1|数量の多い注文がある商品¶
SELECT 商品名
FROM 商品
WHERE 商品コード IN (SELECT 商品コード
FROM 注文
WHERE 数量 >= 3)
内側は外側の列を使っていないので、内側から読みます。注文表で数量が3以上なのは注文1だけなので、内側の結果はP1の1つです。
外側は 商品コード IN ('P1') と読み替えられます。結果はノートの1行になります。
練習2|いちばん安い商品¶
SELECT 商品名, 価格
FROM 商品
WHERE 価格 = (SELECT MIN(価格) FROM 商品)
MINは最小の値を求める関数です。内側は150・120・80・200のうち最も小さい80という1つの値を返します。
外側は 価格 = 80 と読み替えられるので、結果は消しゴム・80の1行です。
練習3|相関副問い合わせで条件を加える¶
SELECT 商品名
FROM 商品
WHERE NOT EXISTS (SELECT *
FROM 注文
WHERE 注文.商品コード = 商品.商品コード)
AND 価格 >= 150
内側に 商品.商品コード が出てくるので、外側の1行ずつ読みます。注文されていないのはボールペンとファイルで、NOT EXISTSが真になるのはこの2行です。
そこに 価格 >= 150 の条件が加わります。ボールペンは120なので外れ、結果はファイルの1行になります。
副問い合わせで間違えやすい点¶
結果が合わなかったときに見直せるよう、つまずきやすい点を表にまとめます。
| 間違えやすい点 | 起きること | 確かめ方 |
|---|---|---|
| 内側の結果を書き出さずに読む | 外側と内側の条件が混ざる | 内側の結果を先に紙に書く |
| INの結果を結合と同じだと思う | 行数を多く数えてしまう | INは外側の行を増やさない、と思い出す |
| = の右に複数行の副問い合わせを置く | エラーになる | 1つの値か集合かを見分ける |
| 相関副問い合わせを内側から実行する | 内側の条件が決まらない | 外側の列が出てきたら1行ずつ読む |
| NOT IN の内側にNULLがある | どの行も残らない | NULLが混ざらないかを確かめる |
どの間違いも、内側が何を返すのかを確かめずに読み進めたときに起きます。手順2の書き出しを習慣にしておけば、こうした間違いは防ぎやすくなります。
副問い合わせの読み方は科目Bでも役に立つ¶
相関副問い合わせを読むとき、外側の商品を1つずつ取り出し、そのたびに注文表を上から調べました。これは、プログラムで二重の繰返しを使い、2つの配列を突き合わせる処理とよく似ています。
外側の繰返しで1つずつ取り出し、内側の繰返しで相手を探し、見つかったら印を付けるという流れです。科目Bで出題される擬似言語の問題でも、こうした突き合わせの処理はよく登場します。
擬似言語とは、試験のために決められた書き方でプログラムを表したものです。科目Bの出題内容を知りたい人は、こちらの記事を読んでみてください。
【関連記事】基本情報技術者試験の科目Bとは?擬似言語・アルゴリズム対策を初心者向けに解説
私たちの Giji Academy では、擬似言語の講座を記法の基礎から条件分岐・繰返し・配列・アルゴリズム、科目B対策まで段階的に用意しています。擬似言語シミュレーターを使えば、プログラムをブラウザ上で1行ずつ実行し、変数の値が変わる様子を確かめられます。
一部の教材と入門レベルの練習問題は、無料会員のまま使えます。講座は擬似言語の講座一覧から始められます。
まとめ¶
副問い合わせ(サブクエリ)は、SQLの中にカッコで包んで書いた、もう1つのSELECT文です。内側の結果を書き出し、1つの値か集合かを見分けてから外側に当てはめると、落ち着いて読めます。
比較演算子と組み合わせる形は、内側が1つの値を返す必要があります。INは集合に含まれるかを調べ、結合と違って外側の行を増やしません。NOT INは、内側にNULLが混ざると、どの行も残らなくなる点に注意しましょう。
EXISTSは、内側に外側の列が出てくる相関副問い合わせとして使われることが多く、外側の1行ずつ内側を調べて読みます。内側に何が返るかを確かめる習慣を付ければ、入れ子のSQLも1つずつほどいて読めるようになります。