はじめに

サブクエリのことを書く前に、Viewの説明をする必要があるため、記載します。

その後、サブクエリのことを記載します。

View(ビュー)とはなにか

テーブルと似たようなものである。

データベースのテーブルデータの格納先はコンピュータ内の記憶装置(ハードディスク)です。

SELECT文でデータを取得しようとした時は、記憶装置からデータを選択して取り出そうとしています。

一方、Viewはテーブルと同じような見た目をしているのにも関わらず、記憶装置にデータは保存されません。

Viewはデータベースのテーブルを必要な時にSELECTして表示しているだけになります。

なぜViewを使うのか

1. データを保存しないため、記憶装置の容量を節約できるため
2. 頻繁に使うSELECT文を使い回すことができるため

Viewの作り方

構文

CREATE VIEW {View名} ({Viewカラム名1},{Viewカラム名2})
AS
{SELECT文}

サンプル(商品テーブルがあり、商品のカテゴリー別に件数を表示する)

CREATE VIEW ShohinSum (shohin_category,shohin_count)
AS
SELECT shohin_category,COUNT(*)
FROM Shohin
GROUP BY shohin_category;

作られるView

サブクエリとは

サブクエリとは、前述しているViewの使い捨てバージョンになります。

サブクエリ使い方

先程、Viewの作り方で解説したSQLがそのまま反映されるようなイメージです。

SELECT *
FROM (
SELECT shohin_category,COUNT(*)
FROM Shohin
GROUP BY shohin_category
) AS ShohinSum;

実行結果(Viewの時と同じです)

実行イメージについて

SELECT文の実行イメージが以下になります。
サブクエリが先に実行され、外側のものが後で実行されます。

---②---
SELECT *
FROM (
---②---
---①---
SELECT shohin_category,COUNT(*)
FROM Shohin
GROUP BY shohin_category
---①---
)
---②---
AS ShohinSum;
---②---

スカラ・サブクエリについて

スカラ・サブクエリとは1行1列だけの戻り値を返すという制限をつけたサブクエリのことです。
(「10」や「文房具」といった1つの値)

必ず1行1列で無いとスカラ・サブクエリとして使用できません。

メリット

1行1列の値を返すため、スカラ・サブクエリの戻り値をWHERE文等の=や<>の比較演算子に使用することができます。

固定値として出してみる

SELECT shohin_id, shohin_tanka,
(SELECT AVG(shohin_tanka) FROM Shohin) AS avg_tanka
FROM Shohin

実行結果

HAVINGと組み合わせて

上記の固定値として出すパターンはあまり無いと思います。
HAVINGと組み合わせることで、
商品カテゴリ(shohin_category)ごとに計算した平均単価が、
全体の平均単価より高い商品カテゴリのみ選択する。
というシチュエーションのクエリが発行できます。

SELECT shohin_category,AVG(shohin_tanka)
FROM Shohin
GROUP BY shohin_category
HAVING AVG(shohin_tanka) >
(SELECT AVG(shohin_tanka) FROM Shohin)

実行結果
| shohin_category | shohin_count |
| ---- | ---- |
| 文房具 | 300 |
| 家電 | 200 |

相関サブクエリについて

商品カテゴリ(shohin_category)ごとに平均販売単価より高い商品を取り出したい場合、
先程のスカラ・サブクエリが使用できないので、相関サブクエリを使用します。

相関サブクエリ使ってみる

SELECT * FROM Shohin AS S1
WHERE shohin_tanka
>
(
SELECT AVG(shohin_tanka)
FROM Shohin AS S2
WHERE S1.shohin_category = S2.shohin_category
GROUP BY shohin_category
);

ポイントは、WHERE S1.shohin_category = S2.shohin_categoryの部分になります。
ここで、S1とS2の紐付けを行うことで、スカラ・サブクエリと同じような感じで1行1列の値が取得できています。