SQL JOIN [もう一歩] 3パターンをおさえよう!

これをマスター

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

テーマ

全ての売上と該当するお客さんを集計してみよう

はじめに

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


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

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

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

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

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

 
テーブル: sales
 取引ID取引日顧客ID商品ID合計金額
174/1/2055852250
2114/1/2078127700
3194/2/2078846500
4324/2/2068323500
5334/2/209934245
6394/3/2066432300
7504/3/2031649600
 
テーブル: customer
 顧客ID氏名顧客歴誕生日性別
199ケリーキー4273918412M
2316Clark Schroeder4274218474M
3664ラファエルエステス4274818598M
4683コリン・リン4275018660M
5788スコット・ホールデン4275618784M
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

SELECT * FROM A a INNER JOIN B b ON a.key=b.key

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

 
 
テーブル: sales
 取引ID取引日顧客ID商品ID合計金額
174/1/2055852250
2114/1/2078127700
3194/1/2078846500
4324/1/2068323500
5334/1/209934245
6394/1/2066432300
7504/1/2031649600
 
テーブル: customer
 顧客ID氏名顧客歴誕生日性別
199ケリーキー4273918412M
2316Clark Schroeder4274218474M
3558エルビス・カルデナス4274518536M
4664ラファエルエステス4274818598M
5683コリン・リン4275018660M
6781イゴールビーチ4275318722M
7788スコット・ホールデン4275618784M
 

LEFT/RIGHT JOIN

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

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

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

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

 
 
テーブル: sales
 取引ID取引日顧客ID商品ID合計金額
174/1/2055852250
2114/1/2078127700
3194/1/2078846500
4324/1/2068323500
5334/1/209934245
6394/1/2066432300
7504/1/2031649600
 
テーブル: customer
 顧客ID氏名顧客歴誕生日性別
199ケリーキー4273918412M
2316Clark Schroeder4274218474M
3558エルビス・カルデナス4274518536M
4664ラファエルエステス4274818598M
5683コリン・リン4275018660M
6781イゴールビーチ4275318722M
7788スコット・ホールデン4275618784M
 

OUTER JOIN

SELECT * FROM A a OUTER JOIN B b ON a.key=b.key

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

 
 
テーブル: sales
 取引ID取引日顧客ID商品ID合計金額
174/1/2055852250
2114/1/2078127700
3194/1/2078846500
4324/1/2068323500
5334/1/209934245
6394/1/2066432300
7504/1/2031649600
 
テーブル: customer
 顧客ID氏名顧客歴誕生日性別
199ケリーキー4273918412M
2316Clark Schroeder4274218474M
3558エルビス・カルデナス4274518536M
4664ラファエルエステス4274818598M
5683コリン・リン4275018660M
6781イゴールビーチ4275318722M
7788スコット・ホールデン4275618784M
 

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で繋げる事が多いです

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

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

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

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

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

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

あわせてご覧ください

SQL 演算子

SQL

SQL Tips

Read More

EXCEL SUBSTITUTE

Excel

Excel EOMONTHの使い方を分かりやすい例で紹介

Read More

SQL DELETE

SQL

SQL Tips

Read More