理系学生日記

おまえはいつまで学生気分なのか

忍者TOOLS

Join

データベースを(第三)正規形にしようとすると,普通,確実にテーブルは 1 つじゃ無理.元々の (繰り返し,冗長を含む) 一つのテーブルは分割される運命にあります.でも,クエリを投げるときは,元々のテーブルを復元するか,復元までいかないまでも,いくつかのテーブルを合体させる必要があります.合体に使われるのが外部キーで,外部キーを使っていくつかのテーブルを合体させる操作が,Join というやつですね.

Join にはいくつかのタイプがあって,FROM 句に単にテーブル名を並べるだけだと,cross join がとられます.結果として返ってくるのが直積集合になってるというパターンです.
 A\times B = \{(a,b)| a \in A, b \in B \}
あんましこの直積集合に (データベース的な意味で) 使い道がないので,通常は INNER JOIN を使うみたい.

SQL92 の仕様にあった文法はこちら.

mysql> SELECT e.fname, e.lname, d.name
    -> FROM employee e INNER JOIN department d
    -> ON e.dept_id = d.dept_id;

でも,昔はこんな書き方もしたとのこと.むしろ,こちらの方を大学でも,試験勉強でも学んだ気がする.

mysql> SELECT e.fname, e.lname, d.name
    -> FROM employee e, department d
    -> WHERE e.dept_id = d.dept_id;

本に書いてあるとおり,JOIN の条件と Filtering の条件を ON,WHERE の使い分けによって分割できるというのは大きなメリットな気はするなー.
ちなみに,サブクエリの結果として現れるテーブルとも JOIN は可能.

mysql> SELECT a.account_id, b.name, a.avail_balance
    -> FROM branch b INNER JOIN
    ->   (SELECT account_id, avail_balance, open_branch_id
    ->    FROM account
    ->    WHERE avail_balance > 1000) a
    -> ON a.open_branch_id = b.branch_id;

テーブル自身との JOIN を取るのが Self-Join,自己結合.テーブル中の外部キーがそのテーブル自身のカラムを参照している場合にどうぞってところ.また,ON 句で指定するときに,外部キーを使って '=' で結合しようとするのを Equi-Join,そうでない (不等号とかでテーブルの JOIN を取る) のを Non-Equi-Join というらしい.これとかが Non-Equi-Join.

mysql> SELECT e1.fname, e1.lname, 'VS' vs, e2.fname, e2.lname
    -> FROM employee e1 INNER JOIN employee e2
    -> ON e1.emp_id < e2.emp_id
    -> WHERE e1.title = 'Teller' AND e2.title = 'Teller';