こんにちは。まるです。
前回まではJava研修の内容をまとめていましたが、今回からはSQLに入ります。
SQLもまだ不慣れな部分が多いので、自身の中で整理しながらアウトプットしてみます。

副問い合わせとは

副問い合わせとは、ざっくり言ってしまえばSQL文の中に記述されて入れ子になっているSELECT文です。サブクエリとも言います。
副問い合わせを使うことで、1つのSQL文で複数の処理を行うことが出来ます。
いくつかのパターンに分けて見てみましょう。

単一行の副問い合わせ

検索結果が1行1列になる、一番シンプルな副問い合わせです。
今回は以下の二つの表をサンプルとして用意しました。

■商品表

■購入履歴

それではまず単一行副問い合わせの一例として、「商品表」から以下の条件の絞り込みを行います。
・商品表に載っている商品のうち、価格が平均価格以上の商品を洗い出し、商品名を一覧化する

SQL文は以下のようになります。

SELECT
	商品名
FROM
	商品表
WHERE
	価格 >= (SELECT AVG(価格) FROM 商品表);

括弧の中身が副問い合わせ文です。
試しに括弧の中身だけでSQLを実行してみましょう。

全商品の平均価格が算出出来ました。ちゃんと1列1行の検索結果になってます。
この結果を元のSQL文に戻すと以下のようになります。

SELECT
	商品名
FROM
	商品表
WHERE
	価格 >= 160;

分かりやすくなりましたね。結果、絞り込みの結果は以下のようになります。

平均価格である160円以上の商品名を一覧化することが出来ました。
単一行副問い合わせは、副問い合わせ文の結果を元のSQL文に代入すれば良いので比較的イメージがしやすいです。

複数行の副問い合わせ

検索結果がn行1列となる副問い合わせです。
先に挙げた「商品表」「購入履歴」を使って、次の絞り込みを行います。
・購買履歴の中で一度も購入されていない商品を洗い出し、商品名を一覧化する

SQL文は以下のようになります。

SELECT
	商品名
FROM
	商品表
WHERE
	商品コード
NOT IN (
	SELECT 商品コード
	FROM 購入履歴
);

「購買履歴の中で一度も購入されていない商品を洗い出し」ということなので、
まず副問い合わせ文の中で、これまでに購入されている商品の「商品コード」を洗い出しています。

なぜ「商品コード」を洗い出しているかというと、「商品表」にも「商品コード」項目があるため、この項目の値を照らし合わせることで「商品表」の値の絞り込みを行うことが出来るからです。

SELECT
	商品名
FROM
	商品表
WHERE
	商品コード
NOT IN (
	これまでに購入された商品コードの一覧
    #分かりやすく文言を入れているだけなので、このSQLをそのまま実行するとエラーになります。
);

副問い合わせの結果、現在SQLの()の中は上記のようになっています。
NOT INを使っているので、ここに記載のWHERE文は「商品表の商品コード」と「これまでに購入された商品コードの一覧」が一致しない行を絞り込む、という条件になります。

例えば、「商品表」の1行目の「商品コード」は”1″です。
副問い合わせ結果の表を見てみると、”1″が入っています。
そのため、「商品表」の1行目はこのWHERE文の絞り込み対象外ということになります。

このように見ていくと、「商品表」6行目の「商品コード」”6″だけが絞り込みの対象となることが分かります。
よって、結果は以下の通りです。

表形式の副問い合わせ

検索結果が表形式となる副問い合わせです。これが一番難しい…。
私が詰まった問題を見ていきましょう。
・10月1日～10月3日の間に購入された「りんご」の合計購入数を表示する
※商品名と合計購入数を1つのテーブルで表示すること

うーん…購入日と購入数は「購入履歴」に存在しているので、 とりあえず そちらのテーブルの絞り込みをしてみます。

SELECT SUM(購入数) 
FROM 購入履歴
WHERE
	商品コード = 1
	AND
	購入日 BETWEEN '2020-10-01' AND '2020-10-03';

合計購入数、ということなのでSUM関数を使って「購入数」列の合算値を出しています。更に「商品コード」と「購入日」を使ってデータの絞り込みを行いました。出力結果を見ても良さげな感じです。
では、こちらの結果を副問い合わせとして、元のSQL文を作成してみます。

SELECT
    商品名,
    (SELECT SUM(購入数) 
     FROM 購入履歴
     WHERE
		商品コード = 1
        AND
		購入日 BETWEEN '2020-10-01' AND '2020-10-03'
	) AS 合計購入数
FROM 商品表;

！？
…商品コードの絞り込みを副問い合わせでやって終わった気になっていたけど、元のSQLで絞り込みしてなかった。
という訳で、正解は以下のようになります。

SELECT
    商品名,
    (SELECT SUM(購入数) 
     FROM 購入履歴
     WHERE
		商品コード = 1
        AND
		購入日 BETWEEN '2020-10-01' AND '2020-10-03'
	) AS 合計購入数
FROM 商品表
WHERE 商品コード = 1;

所感

SQLを見てどんな出力結果になるかは大体分かるようになってきましたが、いざ提示された条件のテーブルを出力する、という部分に関してはまだまだです…。
副問い合わせは、大きな括りとして「単一行」「複数行」「表形式」というパターンを押さえておけば応用が利きそうなので、条件を見てパターンと記述方法をスムーズに判断できるところまで落とし込みたいと思います。

今回はここまでです。
有難うございました！