関連記事
ビュー
テーブルとの違い
- ビュー
- 実際のデータではなく、SELECT文が保存されている
- SELECT文を保存しており、実行した際に一時的に仮想のテーブルを作る
- SELECT文で、ORDER BY句が使えない
- テーブル同様、ビューにも行には順序がないから
- ビューに対する更新(INSERT, DELETE, UPDATE)は制限があり、以下の項目を守る必要がある。ビューとテーブルの更新は連動して行われるため。
- DISTINCTが含まれていない
- FROM句に含まれるテーブルは1つだけ
- GROUP BY句を使用していない
- 元のテーブルを集約しているため、ある1つのレコードを追加するときに、整合性がとれない（1つの定まらない）から
- HAVING句を使用していない
- 実際のデータではなく、SELECT文が保存されている
＊DBのデータは、実際にはコンピュータ内の記憶装置（ハードディスク）に保存されている。
ビューのメリット
- データを保存しないことで、記憶装置の容量を節約できる
- 頻繁に使うSELECT文を、ビューとして保存しておくことで使い回しが効く
-
ビューが含むデータは、元のテーブルと連動しているため、自動的に最新の状態に更新されている
- ビューを参照する = そのSELECT文を実行する ため最新状態のデータを選択できる
-
ビューが含むデータは、元のテーブルと連動しているため、自動的に最新の状態に更新されている
CREATE
CREATE VIEW ビュー名 (ビュー列名1, ..)
AS
SELECT文 ..
SELECT
テーブル同様に使用可能
SELECT ビュー列名1, ..
FROM ビュー名；
ビューへの検索の実行順序
- ビューに定義されたSELECT文実行 ２. その結果に対して、ビューをFROM句に指定したSELECT文が実行される
ビューに対する検索は2つ以上のSELECT文が実行される
＊3つ以上、ビューの上にビューを重ねる多段ビューはパフォーマンスの低下を招くため避ける
DROP
DROP VIEW ビュー名;
-- 多段ビューの作成元となっているビューを削除する場合、それに依存するビューが存在するとエラーになる。
依存するビューごと削除するCASCADEオプション
DROP VIEW ビュー名 CASCADE;
サブクエリ
- 使い捨てのビュー（SELECT文の実行終了後に消去される）
- ビュー定義のSELECT文をそのままFROM句に持ち込んだもの
基本の形
SELECT ビュー列1, ..
FROM ( SELECT 列名1, ..
FROM テーブル名
GROUP BY ... ) AS ビュー名;
-- カッコ()内がサブクエリ
-- この「ビュー名」は使い捨てであり、ビューのように記憶装置（ハードディスク）には保存されない
-- サブ : 下位、 クエリ : 問い合わせ
-- = 一段レベルが下がったSELECT文
まずFROM句の中のSELECT文が実行され、その後に外側のSELECT文が実行される。
＊サブクエリの中にサブクエリ..のように深い階層になると、多段ビューの時同様、パフォーマンスに悪影響
スカラサブクエリ
- 必ず1行1列だけの結果を返すサブクエリ
- つまりただ1つの値
使用される状況（例）
- 全体の平均値より大きい値を持ったレコードのみを取得したいが、集約関数(AVG)をWHERE句に書くことはできないため、SELECT文が書けない
SELECT name, age
FROM User
WHERE age > AVG(age)
-- エラーになる
ここで、スカラ・サブクエリを使用する
SELECT name, age
FROM User
WHERE age > (SELECT AVG(age)
FROM User);
-- SELECT AVG(age) FROM User は、全ユーザー対象の平均年齢が、1つの値として出力されている
サブクエリ同様、スカラ・サブクエリから先に実行され、その後外側のSELECT文が実行される
スカラ・サブクエリが書ける箇所
- 定数や列名を書くことのできる場所はどこでも記述可能
- SELECT
- GROUP BY
- HAVING
- ORDER BY
相関サブクエリ
サブクエリとの違い
- （例）全体の平均年齢ではなく、国ごとの平均年齢より高いユーザーを出力する、のようにグループごとに比較する。
スカラ・サブクエリとの違い
- 結果が2行以上で返されるため、対応関係を示す必要がある
基本の形
SELECT name, age
FROM User AS U1
WHERE age > (SELECT AVG(age)
FROM User AS U2
WHERE U1.age = U2.age
GROUP BY nation);
-- 各ユーザーの年齢と平均年齢の比較を、同じ国籍の中で比較する
-- 相関サブクエリ内のWHERE句で対応関係を示すことで、結果的に実行される各クエリでは、
-- 1つの値が返されており、エラーにならない
テーブル全体ではなく、テーブルの一部のレコードの集合に限定した比較をしたい時に用いる
気をつけること
結合条件は、サブクエリ内に書く
- スコープに関して考えると、外側のSELECT文でのWHERE句にテーブルの別名を定義すると、サブクエリが先に実行されて実行結果が残った状態になると、サブクエリ内で使用してテーブルの別名が使用できないため
SELECT name, age
FROM User AS U1
WHERE U1.age = U2.age
AND age > (SELECT AVG(age)
FROM User AS U2
GROUP BY nation);
-- サブクエリが実行終了した際、U2はもう存在しなくなるので、外側のSELECT文時にエラーが起きる
参考
「SQL ゼロから始めるデータベース操作」