サブクエリとは何か調べてみた
はじめに
サブクエリのことを書く前に、Viewの説明をする必要があるため、記載します。
その後、サブクエリのことを記載します。
View(ビュー)とはなにか
テーブルと似たようなものである。
データベースのテーブルデータの格納先はコンピュータ内の記憶装置(ハードディスク)です。
SELECT文でデータを取得しようとした時は、記憶装置からデータを選択して取り出そうとしています。
一方、Viewはテーブルと同じような見た目をしているのにも関わらず、記憶装置にデータは保存されません。
Viewはデータベースのテーブルを必要な時にSELECTして表示しているだけになります。
なぜViewを使うのか
- データを保存しないため、記憶装置の容量を節約できるため
- 頻繁に使う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
| shohin_category | shohin_count |
|---|---|
| 文房具 | 5 |
| 家電 | 3 |
サブクエリとは
サブクエリとは、前述しているViewの使い捨てバージョンになります。
サブクエリ使い方
先程、Viewの作り方で解説したSQLがそのまま反映されるようなイメージです。
SELECT *
FROM (
SELECT shohin_category,COUNT(*)
FROM Shohin
GROUP BY shohin_category
) AS ShohinSum;
実行結果(Viewの時と同じです)
| shohin_category | shohin_count |
|---|---|
| 文房具 | 5 |
| 家電 | 3 |
実行イメージについて
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
実行結果
| shohin_id | shohin_tanka | avg_tanka |
|---|---|---|
| 1 | 50 | 100 |
| 2 | 90 | 100 |
| 3 | 120 | 100 |
| 4 | 150 | 100 |
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列の値が取得できています。
Discussion