はじめに
本記事は、Googleデータアナリティクスのプロフェッショナル認定証のプログラムより、参照させて頂いています。興味を持った方は、是非受講してみてください。
JOIN を理解する
JOIN は SQL 句のひとつで 関連する列に基づき、2 つ以上のテーブルから 行を結合するために使用します。 基本的には、JOIN は前にご説明した VLOOKUP の SQL バージョンと 考えることができます。
データアナリストがよく使う JOIN は
- INNER
- LEFT
- RIGHT
- OUTER
の 4 種類です。
それぞれの JOIN が実際に何をするのかを 視覚化したものがこちらです。
JOIN は異なるテーブルから一致する、 あるいは 関連する列を結合します。 リレーショナル データベースについて 学習したとき これらの値を主キーと外部キーと呼んだのを 覚えているかもしれません。 主キーは、 各値がそのテーブルで一意である列を指す データベース内の識別子です。 そのテーブルは複数の外部キー、 つまり他のテーブルの 主キーを持つことができます。
- たとえば、従業員に関するテーブルであれば 従業員 ID が主キーであり オフィス ID が外部キーとなる、 ということです。
JOIN はこれらのキーを使って 関連性と対応する値を特定します。
INNER
INNER JOIN は、両方のテーブルで 値が一致するレコードを返す関数です。 テーブルをこのベン図の円のように考えると INNER JOIN は、テーブルが 重なっているところにあるレコードを 返すことになります。 そのレコードを 結果のテーブルに表示するには 両方のテーブルにある キーの値である必要があります。 レコードは両方のテーブルで 一致した場合のみ結合されます。
SQL に JOIN を入力すると、通常は デフォルトで INNER JOIN になります。 データアナリストの多くは、クエリ全体を 入力する代わりに、JOIN を省略記法として 使っています。
LEFT／RIGHT
LEFT JOIN は、左側のテーブルから すべてのレコードを返し 右側のテーブルから一致するレコードのみを 返す関数です。 ここで、どのテーブルが左か右かを 判断する方法をご説明します。 英語や SQL では、 左から右へと読みます。 つまり、最初に書かれているテーブルが左で 2 番目に書かれているテーブルが右です。
- LEFT は JOIN 文の左側にあるテーブル名
- RIGHT は JOIN 文の右側にあるテーブル名
ベン図では、左のテーブル全体に 色がついていますが、これは右のテーブルと 重なっていて、左のテーブルと右のテーブルで 共有しているレコードが 選ばれていることを 示しています。 左のテーブルの各行は 右のテーブルに一致するものがなくても 結果として表示されます。
RIGHT JOIN はその逆のことを行います。 つまり、 右のテーブルからすべてのレコードを返し 左のテーブルからは一致するレコードだけを 結果として返します。 テーブルの順番を入れ替えて LEFT JOIN を使っても、 同じ結果が得られます。
- たとえば、SELECT from table A, LEFT JOIN table Bは、 SELECT from table B, RIGHT JOIN table Aと同じです。
OUTER
OUTER JOIN は RIGHT JOIN と LEFT JOIN を組み合わせて 両方のテーブルの一致する すべてのレコードを返します。 一方のテーブルに一致しないレコードがある場合 もう一方のテーブルに 値のないレコードが作成されます。
JOIN を実際に使ってみる
JOIN を使用すると、 複数のデータソースを扱うのが 非常に簡単になり、テーブル間の関連性を より明確にすることができます。 たとえば、こんな感じです。 複数の部署にまたがる従業員データを 扱っているとします。 従業員テーブル employee と 部署テーブル department があり、 どちらも部門 ID の列を 持っています。 さまざまな JOIN 句を使うことにより それぞれのテーブルから異なるデータを取得し、 それを要約できます。 ここで、部門 ID を持たない従業員を除外し 部門名のある従業員のリストを 取得したいとします。
INNER
部門 ID のレコードは、両方のテーブルで 使用されているので、INNER JOIN を使用すると これらの従業員だけを含むリストを 返すことができます。 ここで簡単に補足すると データアナリストは INNER JOIN に対して JOIN とだけ入力することもあります。 ここではその方法で記述することにします。
さて、実行してみましょう。 これで、従業員名と、部門 ID のある 従業員のみの部門 ID のリストができました。
LEFT
ここで、LEFTを使えば すべての従業員名とその部門のリストを 返すことができます。
クエリを実行すると 従業員の名前と部門を含む 新しいリストが返されます。 しかし、ここで NULL 値があると気がつきます。 これは、正しいテーブル、この場合は department に対応する値がないために 起こります。
OUTER
OUTER JOIN は、従業員の名前と部門を すべて取得します。 SELECT AS と FROM を使って どのデータをどのように取得するかを選択します。 employees テーブルからこれを取得するために department テーブルに FULL OUTER JOIN と入力し 両方からすべてのレコードを 取得する処理を 実行します。 ここで再び ON を使います。 これを実行すると
これらのテーブルからすべての従業員名と 部門が取得されます。 department.name 列、 employee.name 列、role 列には NULLが含まれますが、これは 値が一致しない列を結合しているためです。
