これをマスター

マスター データの紐つけをマスター

テーマ

はじめに

SQLのJOIN (ジョイン)では、複数のデータテーブルを
特定の値(キー)を元に紐づけるINNER JIONの方法をシェアしました

SQL JOIN

INNER JOINは、データテーブルAとデータテーブルBに
一致する全ての行がそれぞれ存在すると言う前提でした

もう少し分かりやすく言うと
INNER JOINでは
売上データと顧客データを顧客IDで紐付けました
顧客データには、売上データにある全ての顧客が入っている前提でした

しかし実務では残念ながら完璧なデータばかりではありません

例えば、レジに長蛇の列が出来てしまってさばくのに必死で
いつもならスキャンする会員カードを取りもらして
顧客データが入ってないケースもあり得ます

こんな場合に、INNER JOINで検索すると
注文データは全てあるけど
同じ顧客IDの顧客データが有るものと無いものがでてきます

テーブル: sales
	取引ID	取引日	顧客ID	商品ID	合計金額
1	7	4/1/20	558	52	250
2	11	4/1/20	781	27	700
3	19	4/2/20	788	46	500
4	32	4/2/20	683	23	500
5	33	4/2/20	99	34	245
6	39	4/3/20	664	32	300
7	50	4/3/20	316	49	600
テーブル: customer
	顧客ID	氏名	顧客歴	誕生日	性別
1	99	ケリーキー	42739	18412	M
2	316	Clark Schroeder	42742	18474	M
3	664	ラファエルエステス	42748	18598	M
4	683	コリン・リン	42750	18660	M
5	788	スコット・ホールデン	42756	18784	M
6
7

売上 sales テーブルの4/1の取引日を見てみて下さい

顧客IDは558と781の2つあります
一方、4/1は忙しくて入れ忘れたのでしょうか
顧客 customer テーブルにはそれらの顧客IDは存在しません


SQL JOIN でやったように
このデータで月末レポートで4月の売上と顧客を全部出そうとして

SELECT a.*,b.[氏名] COUNT( [取引ID]) AS ボリューム FROM sales a INNER JOIN customer b ON a.[顧客ID]=b.[顧客ID]

INNER JOINすると4/1のデータは
売上かつ顧客のデータ両方に存在しているわけでは無いので
無視されてしまいます

JOINはデータのJOINのやり方はSQLを始める時につまずき易いところです

ではどうすれば避けられるのでしょうか？

先に答えを言うと、LEFT JOINと言うのを使います

この3パターンをおさえればOK

ではJOINの代表的な3パターンを詳しく見ていきましょう

INNER
LEFT/RIGHT
OUTER

この3つの考え方を抑えておきましょう
実務で事務処理で使うのは(プログラミングでは他も使いますが)
ほぼ INNER と LEFT JOINが多いです

重複になりますが INNER JOIN から見ていきましょう

INNER JOIN

[顧客ID]がAとBで同じデータを取ってきます

	SELECT a.*,b.[氏名] FROM sales a INNER JOIN customer b ON a.[顧客ID]=b.[顧客ID]
テーブル: sales
	取引ID	取引日	顧客ID	商品ID	合計金額
1	7	4/1/20	558	52	250
2	11	4/1/20	781	27	700
3	19	4/1/20	788	46	500
4	32	4/1/20	683	23	500
5	33	4/1/20	99	34	245
6	39	4/1/20	664	32	300
7	50	4/1/20	316	49	600
テーブル: customer
	顧客ID	氏名	顧客歴	誕生日	性別
1	99	ケリーキー	42739	18412	M
2	316	Clark Schroeder	42742	18474	M
3	558	エルビス・カルデナス	42745	18536	M
4	664	ラファエルエステス	42748	18598	M
5	683	コリン・リン	42750	18660	M
6	781	イゴールビーチ	42753	18722	M
7	788	スコット・ホールデン	42756	18784	M

LEFT/RIGHT JOIN

LEFTは
Aの全てのデータと、Bに共通するデータが存在すれば取得します

RIGHTは
Bの全てのデータと、Aに共通するデータが存在すれば取得します

LEFTとRIGHTのJOINの考え方は同じで
左のテーブルを元にするか右のテーブルを元にするかだけで
見やすいので、LEFT JOINを使うのが一般的です

	SELECT a.*,b.[氏名] FROM sales a LEFT JOIN customer b ON a.[顧客ID]=b.[顧客ID]
テーブル: sales
	取引ID	取引日	顧客ID	商品ID	合計金額
1	7	4/1/20	558	52	250
2	11	4/1/20	781	27	700
3	19	4/1/20	788	46	500
4	32	4/1/20	683	23	500
5	33	4/1/20	99	34	245
6	39	4/1/20	664	32	300
7	50	4/1/20	316	49	600
テーブル: customer
	顧客ID	氏名	顧客歴	誕生日	性別
1	99	ケリーキー	42739	18412	M
2	316	Clark Schroeder	42742	18474	M
3	558	エルビス・カルデナス	42745	18536	M
4	664	ラファエルエステス	42748	18598	M
5	683	コリン・リン	42750	18660	M
6	781	イゴールビーチ	42753	18722	M
7	788	スコット・ホールデン	42756	18784	M

OUTER JOIN

AとBに共通しないデータを取得します

	SELECT a.*,b.[氏名] FROM sales a OUTER JOIN customer b ON a.[顧客ID]=b.[顧客ID]
テーブル: sales
	取引ID	取引日	顧客ID	商品ID	合計金額
1	7	4/1/20	558	52	250
2	11	4/1/20	781	27	700
3	19	4/1/20	788	46	500
4	32	4/1/20	683	23	500
5	33	4/1/20	99	34	245
6	39	4/1/20	664	32	300
7	50	4/1/20	316	49	600
テーブル: customer
	顧客ID	氏名	顧客歴	誕生日	性別
1	99	ケリーキー	42739	18412	M
2	316	Clark Schroeder	42742	18474	M
3	558	エルビス・カルデナス	42745	18536	M
4	664	ラファエルエステス	42748	18598	M
5	683	コリン・リン	42750	18660	M
6	781	イゴールビーチ	42753	18722	M
7	788	スコット・ホールデン	42756	18784	M

LEFT/RIGHT OUTER JOIN

SELECT * FROM A a LEFT JOIN B b ON a.key=b.key WHERE b.key IS NULL
SELECT * FROM A a RIGHT JOIN B b ON a.key=b.key WHERE a.key IS NULL

OUTER JOINの派生としてLEFT/RIGHT OUTER JOINがあります

AやBにだけ存在するデータを取得します

あとがき

ここでシェアしたのは体表的なJOINを単独で行う方法です
実務では、3つ以上のテーブルを組み合わせて
複数のJOINで繋げる事が多いです

頭の良い方や経験豊富な方は新しいデータを見てもすぐに
これとこのテーブルはこの方法で繋いで、あとのテーブルはこうやって繋いで
みたいな事を頭の中で瞬時に考えられます

私のような凡人は、いったんデータを出してみて結果をみつつ
最適な方法を探りながら繋げていく地道な方法で始めます

データベースは作ったチームによって作り方や
データの入力の仕方が異なります

ですので同じテーブルを何度か使ったりしていると
テーブルの癖みたいなのがだんだん分かってきます

あとは作ったクエリを保存して置く事をお勧めします
以前使った事があるテーブルだったりすると調査の時間が大幅に短縮できます
あと、時間が経ってからどういうい条件でデータを取ったかなどの
細かい部分を聞かれてもすぐ答えられます

とかく抽出したデータだけ保存しがちですが
クエリも一緒に保存しておくと
後々似たようなリクエストが来た時にも迅速に対応出来ます