テーブル結合したSQLクエリ結果が増える？
SQLに関する記事です。（初心者向け）
ほぼタイトル通りですが、明確には、1テーブルの中で結合キーが重複しているテーブル同士を結合(inner join)すると「元のテーブルのレコード数より増える件について」理解できなくて困っている方向けの記事です。あるいは慣れてない時は分裂したと感じる場合もあるかもしれません。
※そもそも普通は明細／履歴系テーブルを1キーで繋がないですが、わかりやすくするためにこの記事では繋ぎます。
テーブル同士を結合した状態で、レコードが少ない方だけをSelectで表示しているのに、何故か元のレコード数よりも増えている。しかも、純粋にそれぞれのレコード数を合計した数よりも増える。これは何故でしょうか。
※コードはGoogle BigQueryを例にします。
増えてしまう結合の例とSQLの例
例では、1人のお客様が、お問い合わせしたデータを格納したテーブル1、キャンペーンの参加履歴を格納したテーブル2を例にしています。
テーブル1は2レコード、テーブル2は4レコードあります。
結合キーをcustomeridとして、シンプルにinner joinで2つのテーブルを結合し、Selectでは、customeridとsubjectだけ表示します。
SELECT
tt.customerid,tt.subject
FROM
`sqltest-339506.t_sqltest.campaign2` AS ct
inner JOIN
`sqltest-339506.t_sqltest.toiawase` AS tt
ON
tt.customerid = ct.customerid
出力結果
結果は、【8件】となり、元のレコード数の合計よりも増えてしまいました。
| customerid | subject |
|---|---|
| ycu807 | 質問件名11 |
| ycu807 | 質問件名11 |
| ycu807 | 質問件名11 |
| ycu807 | 質問件名11 |
| ycu807 | 質問件名22 |
| ycu807 | 質問件名22 |
| ycu807 | 質問件名22 |
| ycu807 | 質問件名22 |
元のテーブルのレコード数2件だけの表示にはならなかった。何かおかしいとしても合計6件では？と思われる方もいるかもしれません。
処理を分解して考えていくと、8件である理由がわかりますので、解説します。
レコードが増えてしまう理由
結合しているキーが両方のテーブルの中で複数存在しており、その重複を処理していないSQL文を書いているからというものではあるのですが、この説明だけではわかり辛いと思いますので、処理のプロセスを考えてみます。
テーブルを結合するために一致しているデータを探索するというプロセスを1つ1つ考えていくと、理解しやすくなります。
【1】結合キーを基に一致するデータを探索
テーブル1にある1行目のcustomerid 「ycu807」をキーにして、テーブル2にある「ycu807」を探します。
→結果：テーブル2にある4件がヒットします。
【2】2行目以降も同じように処理
続いて、2行目にある「ycu807」をキーにして、テーブル2にある「ycu807」を探します。
→結果：今回もテーブル2にある4件がヒットします。
これを順番に処理、出力していくと、合計8件になりますね。
| 処理 | customerid | subject |
|---|---|---|
| 1行目処理1 | ycu807 | 質問件名11 |
| 1行目処理2 | ycu807 | 質問件名11 |
| 1行目処理3 | ycu807 | 質問件名11 |
| 1行目処理4 | ycu807 | 質問件名11 |
| 2行目処理1 | ycu807 | 質問件名22 |
| 2行目処理2 | ycu807 | 質問件名22 |
| 2行目処理3 | ycu807 | 質問件名22 |
| 2行目処理4 | ycu807 | 質問件名22 |
selectでテーブル2を軸に書き出そうとしても、テーブルの右と左が逆でも同じような結果になります。（4件に対して2回ずつヒット）
※純粋なselectだけでは、「空気を読んで完全重複レコードは消す」なんて事はしてくれません。Hitしたら全部順番に抽出します。
重複を削除する例
SELECT DISTINCTを使うと重複を除外したものを出力できます。
出力結果は下記のようになります。※後述のサブクエリでも同様の結果
| customerid | subject |
|---|---|
| ycu807 | 質問件名11 |
| ycu807 | 質問件名22 |
▼SELECT DISTINCTを使う例
SELECT DISTINCT
tt.customerid,tt.subject
FROM
`sqltest-339506.t_sqltest.campaign3` AS ct
inner JOIN
`sqltest-339506.t_sqltest.toiawase3` AS tt
ON
tt.customerid = ct.customerid
また、ちょっと番外編ですが、単にテーブル2側にも存在している方と一致している人だけを抽出したい場合、サブクエリも使えます。この場合、DISTINCTを使わなくても、あくまでFROMのテーブルは1つで、Where条件としてテーブル2を参照しているのでレコードは増えません。例を記載しますがここでは深くサブクエリについて言及しません。
▼サブクエリの例
WITH
only1 AS (SELECT customerid FROM `sqltest-339506.t_sqltest.campaign3`)
-- only1というサブクエリを作成
SELECT
tt.customerid,tt.subject
-- ここに変更はなし
FROM
`sqltest-339506.t_sqltest.toiawase3` AS tt
-- テーブル結合は使わない
WHERE
tt.customerid in(select customerid from only1)
-- サブクエリにあるcustomeridと一致したものだけを表示
最後に
こちらは私がSQL始めたばかりの方から質問を受け、言葉だけでは説明が伝わり辛かったのでまとめました。ご自身で学んでいる方も、誰かに教える時でもご自由に活用いただけたら幸いです！