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

サブクエリ 移動平均など

前回の応用編です。 日付、入出金、残高はできましたが、ここからさらに、現在のレコードから3行以内(3行あれば3行、なければそれ以内でできるだけ)のレコードの合計値を出す計算を考えます。 結果だけ先に書 …

no image

SQL基礎 case&groupbyの活用について

主に集計タイプの計算で大活躍するgroupbyについて。 主な用途は集計とカット。特にcase式と連動した集計はかなり使える 例 nameとageで構成されたテーブルがあるとして、年代ごとの人数を出し …

no image

laravelのmigrationに関して

DBのカラムの保守などをする場合のmigrationに関して。 ポツポツ使っていたのですが、しっかりまとめてなかったのでここでまとめて見ようかと思います。 Contents1 カラム定義2 実際の実行 …

no image

MySQLのロックに関して+サブクエリ内のupdate

MySQLのロックに関して。 データベースではデータの整合性を保つために読み書きを一時的に制限する仕組みがあります。(データベースだけではないですが・・・) 大きく分けると共有ロック(書き込みは当然だ …

no image

データベース設計のアンチパターン 複数表結合,大作SQL,Blob型の乱用

データベースのアンチパターンに関して。 以前下記ブログでも書いたんですが設計のスキルに関してもう少し身に着ける必要があるとおもい、チェックします。 論理設計のグレーノウハウ サロゲートキー 論理設計の …