こんにちは！
スマレジテックファームのMichiです！

今回は『SQL総復習⑤　ビューとサブクエリ」編です。

ビュー

ビューとは？

一言でいうと、ビューとは「SELECT文を保存しているテーブル」のことです。

通常のテーブルでは、DBに格納されているデータはコンピュータ内の物理的な記憶装置に保存されます。SELECT文でデータを検索しようとするときは、実際にこの規則装置からデータを引っ張り出して計算を行い、ユーザーへ返すという過程をたどります。

一方、ビューの場合はデータを記憶装置に保存しません。というより、どこにも保存しません。ビューが保存しているのは「SELECT文そのものです」。私たちがビューからデータを取り出そうとするときに、ビューは内部的にそのSELECT文を実行し一時的に仮想のテーブルを作ります。

ビューの作り方

ビューを作成するにはCREATE VIEW文を使います。

CREATE VIEW <ビュー名> (<カラム名1>, <カラム名2>, ...) AS
<SELECT文>

実際に作ってみます。次のようなitemsテーブルがあると仮定します。

+----+------------+-------+----------+
| id |    name    | price | category |
+----+------------+-------+----------+
|  1 | ベッド     | 20000 | 家具     |
|  2 | ノート     |   100 | 文房具   |
|  3 | ボールペン |   200 | 文房具   |
|  4 | シャツ     |  3000 | 衣類     |
|  5 | ホッチキス |   500 | 文房具   |
|  6 | ズボン     |  4000 | 衣類     |
+----+------------+-------+----------+

CREATE VIEW文を作成します。

CREATE VIEW items_by_category (category, count_category) AS
SELECT category, COUNT(*)
FROM items
GROUP BY category;

すると、次のようなビューが作成されます。

+------------+----------------+
|  category  | count_category |
+------------+----------------+
| 家具       |              1 |
| 衣類       |              2 |
| 文房具     |              3 |
+------------+----------------+

これは、CREATE VIEW文内で指定したSELECT句SELECT category, COUNT(*) FROM items GROUP BY category;を実行した場合と全く同じ結果となります。

このビューは通常のテーブルと同じように使用することができます。なので、結果を取り出したいときはSELECT文を書けばOKです。

SELECT * FROM items_by_category;

ビューのメリット

ビューのメリットは大きく分けて2つあります。

ひとつめは、データを保存しないため、記憶装置の容量を節約できることです。
先ほどの例の結果を実際のテーブルとして保存する場合、当然ながらデータ分の記憶装置の保存領域を消費します。しかしながら、ビューが保存しているのはあくまでSELECT文ですので、数行のクエリ文相当の保存領域しか消費しません。

ふたつめのメリットは、頻繁に使うSELECT文をいちいち毎回書かなくても、ビューとして保存しておくことで使いまわしがきくことです。
例に挙げたSELECT文はごくシンプルなものですので、毎回書くことの煩わしさは少ないかもしれませんが、複雑なクエリになるほどビューによる効率化の恩恵は大きくなります。

ビューの注意点

1. ORDER BY句は使えない

ビュー定義のSELECT文にはORDER BY句は使えません。したがって、次のようなビュー定義はエラーとなります*1。

CREATE VIEW items_by_category (category, count_category) AS
SELECT category, COUNT(*)
FROM items
GROUP BY category
ORDER BY category;

ORDER BYが使えない理由は、テーブルと同じくビューには行の順序が存在しないからです（テーブルの並び順はシステムで保証されない）。逆に言えば、ORDER BY以外の句ならば何でも書くことができます。

2. ビューに対する更新

ビューではSELECT文の他に、INSERT、UPDATE、DELETE文を使用することも一応は可能です。ただし、これらの文を使用するには「ビュー作成元のテーブルと競合しない」という制限があります。代表的な例を挙げると、以下のような条件です。

SELECT句にDISTINCTが含まれていない
FROM句に含まれるテーブルが一つだけである
GROUP BY句を使用していない
HAVING句を使用していない

以上からも、INSERT、UPDATE、DELETE文を使用する際には常に、ビューではなく元のテーブルを更新する方がよいと筆者は考えます。

サブクエリ

サブクエリとは？

サブクエリを一言で説明すると、「使い捨てのビューです」。

ビューではSELECT文を記憶領域に保存していましたが、それに対してサブクエリは記憶領域には保存せず、SELECT文をそのままFROM句に持ち込みます。

サブクエリの作り方

先ほどビューを使って出力した結果を、サブクエリを使用して出してみましょう。

SELECT category, count_category
FROM (
    SELECT category, COUNT(*) AS count_category
    FROM items
    GROUP BY category
) AS items_by_category;

先ほどのビューを作成した際に作成したSELECT文を、そのままFROM句に挿入しています。結果は次のようになり、先ほどのビューを作成したときの結果と全く同じです。

+------------+----------------+
|  category  | count_category |
+------------+----------------+
| 家具       |              1 |
| 衣類       |              2 |
| 文房具     |              3 |
+------------+----------------+

サブクエリのメリット

ビューと同じくデータを保存しないため、記憶装置の容量を節約できることが挙げられます。ただし、サブクエリは使い捨てで保存領域を消費しないので、その場で一度きりしか使用しないようなSELECT文の場合はビューよりも優れているといえます。

サブクエリの注意点

1. 実行順はサブクエリ → 通常クエリ

サブクエリを書いた場合は、通常クエリよりも先にサブクエリが実行されます（上の例では、先にFROM句を実行しないと、どこからSELECTしたらよいのか分かりませんね）。実際のSQLの書き順とは異なるので注意しましょう。

2. 最後の「AS <サブクエリ名>」は必須

先ほどの例では、サブクエリの最後にAS items_by_categoryと付けることで、このサブクエリ自体に名前を付けています。これは使い捨ての名前なので、ビューのように記憶装置に保存されることはなく、SELECT文の実行終了後には消えてなくなります。

と書くと、「じゃあ、別名付けなくてもいいんじゃね？」と思うかもしれませんが、これはサブクエリのルールとして決まっているものなので、別名は絶対に付けなければなりません。尚、別名を付けるときはASキーワードを使用しますが、このASは省略することが可能です。

まとめ

今回はビューとサブクエリについて解説しました。
ここまでお読みいただきありがとうございました。

*1:PostgreSQLだけはエラーとならない

Michi's Tech Blog

一人前のWebエンジニアを目指して

SQL総復習⑤　ビューとサブクエリ

ビュー