【SQL入門・基本情報対策】副問合せ(サブクエリ)｜データベースの過去問をSQL文を実行しながら解説③

本記事は、「基本情報技術者試験の過去問解説」をしながら「SQLの練習」を同時にやってみよう！という趣旨の内容です。

こむぎ

一緒にSQLを実行してみたい人は、コードの右上のコピーボタンから簡単にSQL文をコピペできます。ぜひご利用ください。

今回の問題：平成26年春期 午前試験問28

“商品”表，”在庫”表に対する次のSQL文と，同じ結果が得られるSQL文はどれか。ここで，下線部は主キーを表す。

　SELECT 商品番号 FROM 商品

　　WHERE 商品番号 NOT IN (SELECT 商品番号 FROM 在庫)

商品｛商品番号，商品名，単価｝
在庫｛在庫番号，商品番号，在庫数｝

基本情報技術者試験ドットコム（https://www.fe-siken.com/fekakomon.php）

選択肢はこちらです。

テーブル作成・データ挿入

まず、問題で使う2つの表を作っていきましょう。

今回も過去問の解説をわかりやすくするため、列名を「問題文中の表の列名をローマ字にしたもの」に設定します。

（例）”商品番号”を”shouhin_bangou”とする

/*商品表を作成*/
CREATE TABLE shouhin(
	shouhin_bangou CHAR(3),
	shouhin_mei VARCHAR(20),
	tanka INT,
	PRIMARY KEY (shouhin_bangou)
);


/*在庫表を作成*/
CREATE TABLE zaiko(
	zaiko_bangou CHAR(3),
	shouhin_bangou CHAR(3),
	zaiko_su INT,
	PRIMARY KEY (zaiko_bangou)
);

こむぎ

主キーは「PRIMARY KEY (主キー)」で設定します

今回は、元の問題文中に具体的なデータがありません。

しかし選択肢のSQL文を実行して結果を確かめたいので、適当なデータを作って行を挿入します。

挿入する内容は、基本情報技術者試験ドットコムの過去問解説を参考にしました。

/*商品表に行を挿入*/
INSERT INTO shouhin VALUES
	('101','テレビA','100000'),
	('102','テレビB','150000'),
	('103','冷蔵庫A','200000'),
	('104','冷蔵庫A','150000'),
	('105','掃除機A','30000'),
	('106','掃除機A','50000');


/*在庫表に行を挿入*/
INSERT INTO zaiko VALUES
	('201','101','1'),
	('202','105','2'),
	('203','102','3'),
	('204','104','5');

完成した2つの表がこちら

問題文中のSQL文の意味

SELECT 商品番号 FROM 商品

　WHERE 商品番号 NOT IN (SELECT 商品番号 FROM 在庫)

基本情報技術者試験ドットコム（https://www.fe-siken.com/fekakomon.php）

SELECT文の式の中にさらにSELECT文を記述することを、「副問合せ（サブクエリ）」といいます。カッコ内のSELECT文が先に実行されます。

問題のSQL文の意味は次のような意味です（意訳）。

SELECT 商品番号 FROM 商品

商品表から商品番号の列を取り出して表示してね。

WHERE 商品番号 NOT IN (△△)

商品番号がカッコ内で指定した値（ここでは△△）の、いずれの値とも等しくない行を選択してね。

SELECT 商品番号 FROM 在庫

△△にあたる部分。
在庫表から商品番号の列を取り出してね。

「△△に当てはまらない行を取り出す」ので、SQL文全体の意味は次のようになります。

商品表の中から「商品表に存在する商品番号のうち、在庫表の商品番号の列に存在しないもの」を選んで、その商品番号を表示する

こむぎ

実行結果を見た方がわかりやすいかもしれません…

問題文中のSQL文を実行

まず、先ほどのSQL文を実際に実行できるコードに書き換えました。

一緒に実行する際は下のコードをコピペしてくださいね。

/*問題文のSQL文*/
SELECT shouhin_bangou FROM shouhin
	WHERE shouhin_bangou NOT IN (SELECT shouhin_bangou FROM zaiko)

実行結果がこちら

商品番号として103と106が表示されました。

先ほど作った2つの表を合わせて見てみると、「商品表には存在しているが在庫表には存在しない商品番号」が出力されたことがわかります。

選択肢のSQL文を解説

それでは次に、選択肢ア～エのSQL文を実行していきましょう。

• 選択肢の内容
• 選択肢のSQL文の意味
• 実行用のコード
• SQL文の実行結果

をそれぞれの選択肢について記載しました。

選択肢ア

SELECT 商品番号 FROM 在庫

在庫表から商品番号の列を取り出して表示してね。

WHERE EXISTS (△△)

「商品番号が△△の中に存在する」行を選択してね。

SELECT 商品番号 FROM 商品

△△にあたる部分。
商品表から商品番号の列を取り出してね。

/*実行用　選択肢ア*/
SELECT shouhin_bangou FROM zaiko
	WHERE EXISTS (SELECT shouhin_bangou FROM shouhin);

実行結果がこちら

「在庫表に存在する商品番号のうち、商品表の商品番号の列に存在する」行が取り出されました。

表示されたのは、その行の商品番号です。

選択肢イ

SELECT 商品番号 FROM 在庫

在庫表から商品番号の列を取り出して表示してね。

WHERE NOT EXISTS (△△)

「商品番号が△△の中に存在しない」行を選択してね。

SELECT 商品番号 FROM 商品

商品表から商品番号の列を取り出してね。

/*実行用　選択肢イ*/
SELECT shouhin_bangou FROM zaiko
	WHERE NOT EXISTS (SELECT shouhin_bangou FROM shouhin);

実行結果がこちら

「在庫表に存在する商品番号のうち、商品表の商品番号の列に存在しない」行が取り出されました。

条件に合う行が存在しなかったので、「結果なし」が返却されています。

選択肢ウ

SELECT 商品番号 FROM 商品

商品表から商品番号の列を取り出して表示してね。

WHERE EXISTS (△△)

「商品番号が△△の中に存在する」行を選択してね。

SELECT 商品番号 FROM 在庫 WHERE 商品.商品番号 = 在庫.商品番号

在庫表の商品番号のうち「商品表と在庫表の両方に存在する商品番号」を取り出してね。

/*実行用　選択肢ウ*/
SELECT shouhin_bangou FROM shouhin
	WHERE EXISTS (SELECT shouhin_bangou FROM zaiko
		WHERE shouhin.shouhin_bangou = zaiko.shouhin_bangou);

実行結果がこちら

「商品表と在庫表の両方に存在する商品番号」が表示されました。

選択肢エ

SELECT 商品番号 FROM 商品

商品表から商品番号の列を取り出して表示してね。

WHERE NOT EXISTS (△△)

「商品番号が△△の中に存在しない」行を選択してね。

SELECT 商品番号 FROM 在庫 WHERE 商品.商品番号 = 在庫.商品番号

在庫表の商品番号のうち「商品表と在庫表の両方に存在する商品番号」を取り出してね。

こむぎ

商品表に存在する商品番号のうち、選択肢ウで表示されなかった値が取り出されます。言い換えると、商品表には存在するが在庫表には存在しない商品番号が表示されます。

/*実行用　選択肢エ*/
SELECT shouhin_bangou FROM shouhin
	WHERE NOT EXISTS (SELECT shouhin_bangou FROM zaiko
		WHERE shouhin.shouhin_bangou = zaiko.shouhin_bangou);

実行結果がこちら

「商品表には存在するが在庫表には存在しない商品番号」が表示されました。

難しすぎる…

正解は選択肢エ

ようやく答えにたどり着きました。

問題文中のSQL文の実行結果と同じ結果が得られるのは、選択肢エのSQL文です。

おわりに

今回はFE平成26年春期 午前問題の問26を題材に、SQL文の副問合せ(サブクエリ)を練習しました。

選択肢のウとエが特に難しかったですね（説明も悩みました）。

問題をただ解くだけの場合より、少しでも具体的にデータベースをイメージしてもらえていたら幸いです。

こむぎ

お読みいただきありがとうございました！