4.3 副問い合わせとビュー

副問い合わせ(サブクエリ)は、括弧()で囲った問い合わせであり、問い合わせを入れ子にすることによって別の表のデータを使った検索を可能にするものです。ビューは、副問い合わせに名前を付けたものであり、仮想的な表として利用できるものです。

副問い合わせ

副問い合わせには以下の4種類があり、それぞれ利用方法が異なります。

  • スカラ副問い合わせ
  • 単一列副問い合わせ
  • 導出表副問い合わせ
  • 相関副問い合わせ
スカラ副問い合わせ

1行1列の結果、すなわち単一の値を返すSELECT文で、主問い合わせ中で定数と同じように使用できます。

具体例として、orders表を主問い合わせ、prod表を副問い合わせとするSELECT文を以下に示します。

SELECT *
  FROM orders
  WHERE prod_id =
    (SELECT prod_id FROM prod WHERE prod_name = 'りんご');

副問い合わせによってprod表からprod_name列が「りんご」の行のprod_id列の値が返却され、この値とprod_id列が等しい行がorders表から出力されます。副問い合わせの返却値が2つ以上あるとエラーになるので注意が必要です。

単一列副問い合わせ

複数行1列の結果、すなわち値のリストを返すSELECT文で、IN述語を使ってWHERE句の条件として使用できます。

IN述語は、値式が副問い合わせの結果の少なくとも1つと等しいかどうかを判定します。形式は以下の通りです。

値式 [NOT] IN (副問い合わせ | 値式 [, ...])
  • NOTを指定すると、副問い合わせの結果のどれとも等しくない場合に真となります。
  • 副問い合わせの代わりに値式のリストも指定できます。

具体例として、orders表を主問い合わせ、prod表を副問い合わせとするSELECT文を以下に示します。

SELECT *
  FROM orders
  WHERE prod_id IN
    (SELECT prod_id FROM prod WHERE prod_name = 'りんご' OR prod_name = 'みかん');

先ほどのスカラ副問い合わせと異なり、prod_name列が「りんご」と「みかん」に対応する2つのprod_idが返却されます。IN述語により、いずれかのprod_idと等しい行がorders表から出力されます。

導出表副問い合わせ

複数行複数列の結果(導出表)を返すSELECT文で、表別名を付けることにより、以下のようにFROM句の入力表として使用できます。

FROM (副問い合わせ) [AS] 表別名 [(列別名 [, ...])]
  • 表別名は、導出表に付ける新しい表名です。
  • 列別名は、導出表の各列に付ける新しい列名です。省略時は元の列名が使用されます。

具体例として、副問い合わせによって作成した導出表を検索するSELECT文を以下に示します。

SELECT *
  FROM (SELECT * FROM orders WHERE prod_id >= 2) AS "orders_sub"
  WHERE qty >= 5;

副問い合わせによって、orders表からprod_id列が2以上の行を抽出した導出表orders_subを作成し、そこからqty列が5以上の行を出力しています。

相関副問い合わせ

主問い合わせで使われている表を参照する副問い合わせで、EXISTS述語を使ってWHERE句の条件として使用できます。

EXISTS述語は、副問い合わせの結果が1行以上存在するかどうかを判定します。形式は以下の通りです。

値式 [NOT] EXISTS (副問い合わせ)
  • NOTを指定すると、副問い合わせの結果がない場合に真となります。

具体例として、相関副問い合わせとEXISTS述語を用いたSELECT文を以下に示します。

SELECT prod_id, prod_name FROM prod
  WHERE EXISTS
    (SELECT * FROM orders
      WHERE orders.prod_id = prod.prod_id);

このSELECT文は、orders表に存在するすべてのprod_idについて、prod表からprod_id列とprod_name列を出力するものです。その動作は、prod表のprod_id列の値を1つずつ副問い合わせに送り、その値がorders表に存在するかどうかをEXISTS述語で判定しています。

以下のSELECT文でも同じことができますが、最初にorders表全体をスキャンする必要があるため、EXISTS述語を用いた方が速く実行できる可能性があります。

SELECT prod_id, prod_name FROM prod
  WHERE prod_id IN
    (SELECT prod_id FROM orders);

 

ビュー

前節で、FROM句で使用できる導出表副問い合わせについて説明しましたが、副問い合わせの結果は主問い合わせの終了とともに自動的に消滅します。ビューは、副問い合わせに名前を付けて保存することにより、表と同じようにいつでも利用できるようにするものです。ただし、ビューは以下の点で表と異なります。

  • 保存されるのはビューの作成方法のみで、データ実体は保存されません。従って、ビューの元になる表の内容を更新するとビューの内容も自動的に更新されます。
  • 更新可能なビューの場合は、ビューの内容を更新すると元の表の内容も自動的に更新されます。なお、ビューが元の表の列を単純に参照しているだけの場合に更新可能になります。

ビューの作成はCREATE VIEW文、削除はDROP VIEW文で行います。

CREATE VIEW文

CREATE VIEW文は、データベースに新しいビューを作成します。形式は以下の通りです。

CREATE [OR REPLACE] VIEW ビュー名 [(列名 [, ...])]
  AS SELECT文
  • OR REPLACEを指定すると、同じ名前のビューが存在している場合にビューを置き換えます。ただし、ビューの列構成は同じでなければなりません。
  • ビュー名は、作成するビューの名前です。
  • 列名のリストは、ビューを構成する列の名前です。SELECT文で出力される列と対応している必要があります。省略時は、SELECT文で出力される列名になります。
  • SELECT文は、ビューのデータ実体(導出表)を作成する任意のSELECT文です。FROM句には表とビューが指定できます。
    更新可能なビューを作成する場合は、単一の表または更新可能なビューから行と列を単純に出力するSELECT文でなければなりません。

具体例として、orders表からprod_id列が2以下の行のみを抽出したorder_view1ビューを作成するCREATE VIEW文を以下に示します。

CREATE VIEW order_view1 AS
  SELECT order_id, customer_id, prod_id, qty
    FROM orders WHERE prod_id <= 2;

order_view1ビューは更新可能なビューです。このビューに対して、以下のようなSELECT文やUPDATE文を使うことができます。

  SELECT * FROM order_view1 WHERE customer_id = 1;
  UPDATE order_view1 SET qty = 12 WHERE order_id = 1;

上記UPDATE文の実行によって、ビューの元になっているorders表のorder_id列が1の行のqty列が変更されます。

次の例は、orders表、customer表、prod表を結合したビューを作成するCREATE VIEW文です。

CREATE VIEW order_view2 AS
  SELECT order_id, order_date, customer_name, prod_name, qty
    FROM orders AS o JOIN prod AS p1 ON o.prod_id = p1.prod_id
        JOIN customer AS c ON o.customer_id = c.customer_id;

order_view2ビューは、結合されているため更新不可能なビューです。更新はできませんが、以下のようなSELECT文で検索することができます。

SELECT * FROM order_view2 WHERE customer_name = '鈴木物産';

ビューを使うことにより、結合を含んだむ複雑な問い合わせを書く必要がなくなります。

DROP VIEW文

DROP VIEW文は既存のビューを削除します。形式は以下の通りです。

DROP VIEW [IF EXISTS] ビュー名 [, ...]
  • IF EXISTS を指定すると、既存のビューが存在しなくてもエラーになりません。
  • ビュー名は、削除するビューの名前です。複数指定できます。

ビューを削除しても、元になっている表やビューは削除されません。また、他の表やビューから参照されているビューは削除できません。

具体例として、order_view1ビューとorder_view2ビューを削除するDROP VIEW文を以下に示します。

DROP VIEW order_view1, order_view2;