SQLマスターへの道 (INNER JOIN、LEFT JOINでのデータ集計)

はじめに

こんにちは、株式会社iimonでフロントエンドエンジニアを担当している「たーくん、たー坊」です♪

本記事はアドベントカレンダー15日目の記事になります。

フロントエンジニアながらサービスの運用・保守をするにあたりSQLでデータを見ることがあったり、バックエンドにも今後挑戦したいということもありSQLを勉強しています。

1からSQLを学んでいくと結合あたりから複雑になってくる印象があったので、実際にデータを作成して結合の使い方を整理してみました。

結合とはなに？？

結合とは複数のテーブルをテーブル間で共通するキーを使用してまとめることをいいます。

データベースでは通常、関連する情報が異なるテーブルに分割されています。

この異なるテーブルを結合することで1つの結果セットとしてまとめることができます。

たとえば、下のような注文テーブルと商品テーブルがあった場合を考えてみましょう。

この2つのテーブルの情報を元に購入金額の合計を使用者ごとに求めたい場合、注文テーブルからは誰が何を購入したかはわかる状態になっていますが、商品の値段がないので注文テーブルだけでは使用者単位での合計金額を算出することはできなそうです。

また、商品テーブルには商品IDと値段の情報がありますが、使用者の注文に関する情報がないので商品テーブルだけでも合計購入金額を算出することはできなそうです。

ここでテーブル結合を使わないと注文テーブルと商品テーブルのデータを取得して、それをエクセルやスプレッドシート上などで合体させて集計するみたいな手法をとるしかなくなってくると思います。

このように2つのテーブルからデータを取得して別の場所でわざわざ集計するのはかなり面倒くさいのです。

そこでテーブルを組みあせて、そのテーブルに対して検索をしたり集計するのが効率がよさそうです。(テーブル結合)

下の画像のように注文・商品テーブルを組みあわせた新しいテーブルを作成することで、使用者ごとの購入金額を簡単に計算することができそうです。(共通の商品者IDを元にテーブル結合)

次は結合の仕方について見ていきましょー♪

内部結合

内部結合 (INNER JOIN) は、SQLにおいて2つ以上のテーブルを結合する方法の一つで、結合条件を満たす行のみを結果セットに含める結合方法です。

結合するテーブルの間で共通する値を基準に条件を満たす行だけが結果に含まれ、結合条件に一致する行が片方のテーブルに存在しない場合、その行は結果に含まれません。

外部結合

外部結合 (OUTER JOIN) は、SQLでテーブルを結合する方法の一つで、結合条件に一致しない行も含めて結果を取得する方法です。内部結合 (INNER JOIN) とは異なり、条件に合わないデータも結果に表示され、該当しない部分はNULLで補完されます。

外部結合には、左側のテーブル（指定した最初のテーブル）を基準に、右側のテーブルと結合するLEFT OUTER JOIN、右側のテーブル（指定した2番目のテーブル）を基準に、左側のテーブルと結合するRIGHT OUTER JOIN、両方のテーブルを基準にし、条件に一致しないデータも全て含めるFULL OUTER JOINの3種類があります。ですが、MySQLで FULL OUTER JOIN 構文は使えません。

それぞれのメリットと適しているケース

内部結合のメリット

条件に一致するデータだけを扱うため、結果がシンプルで効率的
データが整合性を持つので、分析や計算で問題が発生しにくい
パフォーマンスが良く、処理負荷が軽減される
NULL値の扱いを気にせず実装可能で、クエリが簡単

内部結合のデメリット

条件に一致しないデータは結果から完全に除外され、欠損データの有無や不整合を確認ができない
全体的なデータの状況（例えば、一部のデータが欠落している割合など）を把握するのが難しくなる
NULL値を含むデータを確認する、または分析に利用する場合、内部結合では除外されてしまう

内部結合を選ぶべきケース

条件に一致するデータだけが必要な場合。
欠損データや不一致データが不要で、結果をシンプルに保ちたい場合。
パフォーマンスを重視し、不要なデータを除外したい場合。

外部結合のメリット

データの欠損や不整合を調査するのに適している
NULLデータを含む行も保持できる
データの全体像を把握できる

外部結合のデメリット

NULL を適切に扱わないと、データ分析や後続の処理でエラーや不正確な結果を引き起こす可能性がある。
条件に一致しないデータまで結果に含まれるため、必要以上のデータが返されることがある
データ量が大きくなりやすいので、クエリの実行時間が長くなることがある。

外部結合を選ぶべきケース

欠損データや不一致データを含めて分析したい場合。
全体像を把握したい場合。
データの欠損や不整合を調査したい場合。

基本構文

INNER JOIN

SELECT 列名1, 列名2, ...
FROM テーブル1
INNER JOIN テーブル2
ON テーブル1.列名 = テーブル2.列名;

LEFT OUTER JOIN

SELECT 列名1, 列名2, ...
FROM テーブル1
LEFT OUTER JOIN テーブル2
ON テーブル1.列名 = テーブル2.列名;

RIGHT OUTER JOIN

SELECT 列名1, 列名2, ...
FROM テーブル1
RIGHT OUTER JOIN テーブル2
ON テーブル1.列名 = テーブル2.列名;

実際に使ってみた

会社を想定して担当しているプロジェクトを通じて一生懸命頑張っている従業員とサボっている従業員を表示します。

従業員のテーブル(employees)

name 従業員の名前

department 部署名

プロジェクトのテーブル(projects)

empoyee_id employeesに対応するID

project_name プロジェクトの名前

まずは内部結合を使って、プロジェクトに割り当てられている従業員の情報とそのプロジェクトの名前を取得する。

(各テーブルのemployee_idを使用して結合)

sql

SELECT employees.employee_id, employees.name, employees.department, projects.project_name
FROM employees
INNER JOIN projects
ON employees.employee_id = projects.employee_id;

結果

プロジェクトのテーブルのemploye_id からプロジェクトに関わっているのは1(Taro)、2(Megumi)、4(Ren)だと分かりますが、プロジェクトが割り当てられていない従業員の情報や、従業員が担当していないプロジェクトは結果に含める必要がない場合にはこのように内部結合を使うのが適しています。

プロジェクトが割り当てられていない(projectsテーブルのemployee_idがない)3(Shogo)、5(Kumi)は結果から除外されています

これで仕事を頑張っている人が誰かが不要な情報を含めることなく表示することができました。

次は外部結合(LEFT JOIN)を使ってみたいと思います。

sql

SELECT employees.employee_id, employees.name, employees.department, projects.project_name
FROM employees
LEFT JOIN projects
ON employees.employee_id = projects.employee_id;

結果

外部結合を使って全従業員の一覧情報を表示し、それぞれが担当しているプロジェクト（またはプロジェクトがない場合も含む）を確認することができます。

プロジェクトがない従業員の行はNULL で表示されます。

これでproject_name が割り当てられてないShogoとKumiにProject Eを割り当てればいいことが分かりました

ちなみにこれをRIGHT JOIN をつかって書くと以下のようになります

sql

SELECT employees.employee_id, employees.name, employees.department, projects.project_name
FROM projects
RIGHT JOIN employees
ON employees.employee_id = projects.employee_id;

LEFT JOIN と比べてRIGHT JOIN は右側のテーブル（ここでは employees）を基準とし、すべての行を保持しています。

このように基準テーブルの左はFROM 句のあと、右はJOIN 句にあるテーブルになります

個人的には、左（基準）テーブルを先に指定する方が直感的で読みやすいためLEFT JOINを使ったほうがよいと思っています

注意すべき点

内部結合と外部結合をつかうことでそれぞれ集計数が変わるので意識して使う必要があります。

先述のように内部結合をつかったときのレコード数が4つなのに対し、外部結合を使ったときのレコード数が6つ。それぞれの仕様をよく理解して使うことが非常に大事です

実際に結合したテーブルからデータを集計する(内部結合)

今度は購入者情報テーブル(user_info)と注文テーブル(order_info)から男女別の平均購入単価だしてみる。今後どちらの性別をターゲットに商品をだすかを決める大事な集計です。

テーブルの内容は以下になります。

user_info

order_info　

order_info のテーブルには購入したユーザーが男性か女性かを知るための情報が入ってないので、性別の情報が入っているuser_info のテーブルと結合して、どちらかが購入しているか判断する必要があります。

この場合、平均購入単価を知りたいので購入していない人のデータは不要のため、内部結合(INNER JOIN)を使用します。(各テーブルのuser_idを元に結合)

sql

SELECT 
    order_id, 
    item_id, 
    price, 
    user_info.gender
FROM 
    order_info
INNER JOIN 
    user_info
ON 
    order_info.user_id = user_info.user_id;

結果

これだけでは、男女別の平均購入単価がでていないので、下記sqlで男女別の平均購入単価を表示します。

sql

SELECT 
    user_info.gender,
    FLOOR(AVG(price)) AS avg_price
FROM 
    user_info
INNER JOIN 
    order_info
ON 
    user_info.user_id = order_info.user_id
GROUP BY
    gender;

sqlの説明

まず、GROUP BY でgender ごとにデータをグループ化します。

GROUP BY直後のテーブルイメージ

これだと、男性や女性に対して複数の価格が存在することになり、SQLがどの値を返すべきかが決められない状態です。

この集計可能なリストに対して指定された項目の平均値を返す集計関数のAVG関数を使うことでグループ化された各グループ内のデータを1つに集約できます。つまり各性別の集計可能なリストの平均値を出すことで1つにデータを集約できます。

その他

FLOOR →引数として与えられた数値を小数点以下を切り捨てた整数に変換

AS avg_price →FLOOR(AVG(price))の計算結果にavg_priceという名前をつけている(エイリアス)

結果

これで女性の方が購入単価が高いことがわかりました！

実際に結合したテーブルからデータを集計する(外部結合)

次は商品テーブル(products)と注文テーブル(orders)のテーブルから売れたことのない商品を表示します。売れたことのない商品は製造をやめてしまいましょう。

テーブルの内容は以下になります。

商品テーブル(products)

注文テーブル(orders)

今回は売れたことのない商品を表示するので商品に紐づいていないorder_idつまりorder_id がnullのデータを取得したいので、今回は外部結合(LEFT JOIN)を使用します。

まずは外部結合してみましょう。

sql

SELECT 
    *
FROM 
    products
LEFT JOIN 
    orders 
ON 
    products.product_id = orders.product_id;

結果

これで注文がない商品の箇所にはorders_id にnull が入ってることがわかります。

今回はデータが少ないのでテーブルのデータ一覧を見ることができますが、たくさんのデータがある場合は一覧にして全部のデータを見るのはとても大変なのでorders_id にnull が入っているデータだけを取得しましょう。

sql

SELECT 
    *
FROM 
    products
LEFT JOIN 
    orders 
ON 
    products.product_id = orders.product_id
WHERE 
    orders.order_id IS NULL;

説明

WHERE orders.order_id IS NULL は、orders テーブルに対応するデータがない行を抽出する条件になります。

結果

売れたことがない商品はコーヒーとメロンパンということがわかりました。

最後に

今回はデータ量も少なく、単純なsqlしかまとめていないのでパフォーマンスなどは考慮していませんが、実際にデータ量が多い場合だと、内部結合と外部結合のどちらを使うかによってだいぶパフォーマンスが変わってくると思うので、2つのうちどちらがより適した使い方かを考えながら使うのが大事になってくると思いました。

ここまで読んでくださり、ありがとうございます

現在弊社ではエンジニアを募集しています！

この記事を読んで少しでも興味を持ってくださった方は、ぜひカジュアル面談でお話ししましょう！

iimon採用サイト / Wantedly / Green

次のアドベントカレンダーの記事は弊社エンジニア内スーパームードメーカー、あめくさんです！スーパースペシャルな記事たのしみにしてます！！！