skillup

技術ブログ

Database

外部結合 応用編2

投稿日:

引き続き結合についてです。

1対Nの結合に関して

下記のようなテーブルが2つあり、商品ごとの売上のサマリーを出したいとします。

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は下記のようになります。

ただしこれは中間テーブル的なものを作っているのでパフォーマンスに若干の問題があります。
この場合、マスタのitemsに明細をつなぎ、あとからグルーピングすれば行数は増えません。

結合は一対一でなくても一対多なら行数は増えない、ということを覚えておきましょう。

完全外部結合

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 | 西園寺

id | name | name
—-+——–+——–
1 | 田中 | 田中
2 | 鈴木 | 鈴木
3 | 伊集院 |
4 | | 西園寺

外部結合とはこの場合、class_aとclass_bの和集合を求めていることになります。(ベン図を書くとわかりやすいかも。)

COLALESCEは可変個の引数をとって、NULL出ないものを出力する標準関数です。

また完全外部結合が存在しないデータベースもありますので、その場合はLEFT JOINを使ってUNIONでテーブル同士をつなげます。

次にそれぞれ結合により下記のケースを求めてみましょう。

差集合(class_aだけに存在するものとclass_bだけに存在するもの)

class_aのみ

class_bのみ

片方だけに存在しているものの抽出

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

transactionが切れた場合のロックの復旧方法

transactionをスタートしたまま、commitせずにプログラムを途中で止めた場合の処理について。 不用意にプログラムを止めないようにしましょう。 Contents1 ロックのメカニズム1.1 …

no image

アンチパターン データ分身の術+DBの不要な連携+バージョンアップ未テスト

今回のアンチパターンは主にデータ設計に関する部分。 Contents1 同一データの使用1.1 デメリット1.2 対策2 DBの不要な連携2.1 デメリット2.2 対策3 サーバーの移行やバージョンア …

no image

アンチパターン 連鎖倒産+エラー監視+データのバックアップ

本日は主にインフラの設計的なことに関して。 Contents1 連鎖倒産1.1 デメリット1.2 対策2 エラー監視2.1 デメリット2.2 対策3 データのバックアップ体制3.1 デメリット3.2 …

no image

JPAを使用する  JavaSE編

現在の開発ではデータベースへの接続ではJPAを使っています。このJPAがなかなか曲者でいろいろと詰まることがありましたので解説させていただきます。 Contents1 JPAとは?1.1 メリット1. …

no image

CASE式のすすめ その3

本日もCASE式です。 下記のようなテーブル(studentclub)があるとします。 std_id | club_id | club_name | main_club_flg —&#82 …