SQLの絵本 - 6章 複雑なデータ操作

SQL

SQLの絵本―データベースがみるみるわかる9つの扉のメモ。

概要

  • 結合: 複数のテーブルやビューを連結する
    • 交差結合: すべての行を組み合わせる
    • 内部結合: 指定した列の値が一致したデータのみを取り出す
    • 外部結合: 一致しなかったデータも取り出す
      • 左外部結合
      • 右外部結合
      • 全外部結合
  • 集合演算子や限定述語
    • UNION, UNION ALL, INTERSECT, EXPECT, MINUS

いろいろな結合

交差結合

テーブルの全てのデータの組み合わせパターンを作成。
CROSS JOINを使って記述する。

SELECT * FROM tbl_a CROSS JOIN tbl_b;

FROMに続いてカンマでテーブル名を列挙しても同様。

SELECT * FROM tbl_a, tbl_b;
内部結合

指定した列の値が一致するデータのみを取り出す。
INNER JOINを用い、ON以下に基準とする列を指定する。

SELECT * FROM tbl_name INNER JOIN tbl_age ON id = no;

列の名前がテーブルごとに異なる場合は問題ないが、同じ名前の列を参照する場合はピリオドを使って列名をテーブル名で修飾することができる。

SELECT * FROM tbl_namelist INNER JOIN tbl_grades ON tbl_namelist.no = tbl_grades.no;

交差結合とWHERE句を使って次のように記述しても同様。

SELECT * FROM tbl_namelist, tbl_grades WHERE tbl_namelist.no = tbl_grades.no;
外部結合

外部結合では指定した列の値が一致しなかったデータに付いても取り出す。
テーブルaとbをidを基準に結合したとき、aのあるidがbに存在しなければ、結合後にそのidのbに対応する値は存在しないのでNULLとなる。
このとき、結合の方法には3通り考えられる。

  1. aのidを基準にして表を作成する(=bにのみ存在するidは無視する)
  2. bのidを基準にして表を作成する(=aにのみ存在するidは無視する)
  3. aのidもbのidも考慮して表を作成する

テーブル名をa、bの順で記述している場合、1を左外部結合、2を右外部結合、3を全外部結合と呼ぶ。
左外部結合はLEFT OUTER JOIN、右外部結合はRIGHT OUTER JOIN、全外部結合はFULL OUTER JOIN。OUTERは省略できる。
FULL OUTER JOINについてはRDBMSによって使えるものと使えないものがあり、MySQLでは使えない。

SELECT * FROM tbl_name LEFT JOIN tbl_age ON id = no;
SELECT * FROM tbl_name RIGHT JOIN tbl_age ON id = no;
SELECT * FROM tbl_name FULL JOIN tbl_age ON id = no;

ビュー

テーブルから一部分を抜き出した仮想的な表をビューと呼ぶ。
ビューを通じてもとのテーブルのデータの更新などができる。

ビューの作成

ビューはCREATE VIEWにSELECTを渡すことで作成する。

CREATE VIEW viw_ateam AS SELECT sei, mei FROM tbl_meibo WHERE team = 'A';

結合を用いて複数のテーブルを元にビューを作成することも出来る。

CREATE VIEW viw_meibo AS SELECT mei, age FROM tbl_meibo, tbl_nenrei WHERE tbl_meibo.no = tbl_nenrei.no;
ビューを使ったデータの登録、更新、削除

ビューはテーブルと同じようにデータの登録、更新、削除ができる。
複数のテーブルを元にしていたり、GROUP BY句などによりグループ化された行を含むビューは変更できない。
ビューに対する変更はもとのテーブルにも反映される。
ビューの削除はDROP VIEWで行う。

DROP VIEW viw_winner;

集合演算子

集合演算では2つのSELECT文の結果を加算、積算、減算させられる。
2つのSELECT文の結果は列の数とデータ型が一致している必要があるが、列名は異なっていてもよい(左のSELECT文の結果に合わせられる)。

UNION

2つのSELECT文をUNIONでつなぐと、データの和集合が得られる。

SELECT sei, mei FROM tbl_club_a UNION SELECT myoji, namae FROM tbl_club_b;

UNIONでは重複するデータはまとめられるが、代わりにUNION ALLを用いると重複するデータもまとめられずに取り出される。

INTERSECT

データの積集合(共通部分)を求める。OracleとPostageSQLでは利用可能だがMySQLでは使えない。

EXCEPT MINUS

PostageSQLではEXCEPT、OracleではMINUSにより差集合を求められる。
左側のデータを基準に右側のデータに含まれないものを取り出す。

限定述語

限定述語を使うとサブクエリの結果を比較条件として使える。

ALL

ALL演算子は比較演算子と共に用い、サブクエリにより取り出された全ての値との比較を行う。
取り出された全ての値との比較がTRUEになる場合にTRUEを返す。

SELECT kondate FROM tbl_kondate1 WHERE kakaku > ALL (SELECT kakaku FROM tbl_kondate1 WHERE LIKE '%鶏%');
EXITS

EXISTS演算子は続くサブクエリによってデータが一つでも取り出されればTUREを返す。
NOT EXISTS演算子は逆の意味を持つ。

ANY

ANY演算子は比較演算子と共に用い、サブクエリにより取り出されたすべての値との比較を行う。
取り出された値のうちいずれかとの比較がTRUEになればTRUEを返す。
SOMEはANYと同じ意味を持つ。
Oracleではサブクエリではなくカンマで区切った定数を指定することができる。