引き続き結合についてです。
Contents
1対Nの結合に関して
下記のようなテーブルが２つあり、商品ごとの売上のサマリーを出したいとします。
items
item_no | item
———+——
10 | FD
20 | CD-R
30 | MO
40 | DVD
SalesHistory
sale_date | item_no | quantity
————+———+———-
2007-10-01 | 10 | 4
2007-10-01 | 20 | 10
2007-10-01 | 30 | 3
2007-10-03 | 10 | 32
2007-10-03 | 30 | 12
2007-10-04 | 20 | 22
2007-10-04 | 30 | 7
ここから下記のような表を作ることを考えます。
item_no | item | sum_quant
———+——+———–
30 | MO | 22
20 | CD-R | 32
10 | FD | 36
40 | DVD |
これを作り出すSQLは下記のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT i.item_no, i.item, s.sum_quant FROM items i LEFT JOIN ( SELECT item_no, SUM(quantity) as sum_quant FROM SalesHistory GROUP BY item_no ) AS s ON i.item_no = s.item_no |
ただしこれは中間テーブル的なものを作っているのでパフォーマンスに若干の問題があります。
この場合、マスタのitemsに明細をつなぎ、あとからグルーピングすれば行数は増えません。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT i.item_no, i.item, SUM(quantity) FROM items i LEFT JOIN SalesHistory sh ON i.item_no = sh.item_no GROUP BY i.item_no |
結合は一対一でなくても一対多なら行数は増えない、ということを覚えておきましょう。
完全外部結合
JOINで使うものはほとんどがINNER JOINかLEFT JOINでした。RIGHT JOINに関してはそもそも結合させる側をどちらに持ってくるかで変わってくるため、ほとんどつかいませんでした。
LEFT OUTER JOINですが、使ってませんし、理解もあまりしておりませんでした。これを機にしっかり理解しておこうと思っております。
まずは具体例から見ていきましょう。
下記のような2つのテーブル(class_a,class_b)があるとします。
id | name
—-+——–
1 | 田中
2 | 鈴木
3 | 伊集院
id | name
—-+——–
1 | 田中
2 | 鈴木
4 | 西園寺
|
1 2 3 4 5 6 7 8 9 |
SELECT COALESCE(class_a.id, class_b.id) AS id, class_a.name, class_b.name FROM class_a FULL OUTER JOIN class_b ON class_a.id = class_b.id |
id | name | name
—-+——–+——–
1 | 田中 | 田中
2 | 鈴木 | 鈴木
3 | 伊集院 |
4 | | 西園寺
外部結合とはこの場合、class_aとclass_bの和集合を求めていることになります。(ベン図を書くとわかりやすいかも。)
COLALESCEは可変個の引数をとって、NULL出ないものを出力する標準関数です。
また完全外部結合が存在しないデータベースもありますので、その場合はLEFT JOINを使ってUNIONでテーブル同士をつなげます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT class_a.id, class_a.name, class_b.name FROM class_a LEFT JOIN class_b ON class_a.id = class_b.id UNION SELECT class_b.id, class_b.name, class_a.name FROM class_b LEFT JOIN class_a ON class_b.id = class_a.id |
次にそれぞれ結合により下記のケースを求めてみましょう。
差集合(class_aだけに存在するものとclass_bだけに存在するもの)
class_aのみ
|
1 2 3 4 5 6 7 8 9 10 |
SELECT class_a.id, class_a.name FROM class_a LEFT OUTER JOIN class_b ON class_a.id = class_b.id WHERE class_b.id is NULL |
class_bのみ
|
1 2 3 4 5 6 7 8 9 10 |
SELECT class_b.id, class_b.name FROM class_a RIGHT OUTER JOIN class_b ON class_a.id = class_b.id WHERE class_a.id is NULL |
片方だけに存在しているものの抽出
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT COALESCE(class_a.id, class_b.id) AS id, COALESCE(class_a.name, class_b.name) AS name FROM class_a FULL OUTER JOIN class_b ON class_a.id = class_b.id WHERE class_a.id is NULL or class_b.id is NULL |
