サブクエリとは何か調べてみた

はじめに

サブクエリのことを書く前に、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

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列の値が取得できています。