執筆者：Handbook編集部
BigQueryのJOIN演算についてまとめる
BigQuery の JOIN 演算についてまとめる
BigQuery の JOIN 演算が使えるととても便利ですので、この記事は JOIN 演算の使い方について書いていきます。
JOIN 演算
JOIN演算は 2 つのテーブルを結合して、SELECT句でそれらを 1 つのソースとしてクエリできるようにします。さらに結合の条件（ON句とUSING句）によって、2 つのテーブルでレコードの結合と廃棄を行い、1 つのソースを形成する方法が指定できます。
BigQuery の公式ドキュメントではJOIN演算の書き方はかなり詳しく書かれていて、詳細はそちらで確認していただければと思います。本記事は、JOINの使い方をよりイメージしやすくするために例を中心に説明していきます。
[INNER] JOIN
INNER JOIN または単に JOIN を使ってテーブルを結合すると、指定した条件が両方のテーブルでマッチするレコードのみが取得されて結合します。結合条件を満たさないすべてのレコードを廃棄します。
下記は、結合条件のON句を使い、テーブル A 中の x の値とテーブル B 中の y の値が一致するレコードを結合する例になります。
FROM A INNER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 4 | n | +---------------+
+-------+ +-------+
結合条件USING句を使って、同様な効果が出ます。USING句を使う場合は両者のテーブルに同じ名前のカラムが存在しないといけません。
FROM A INNER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +-----------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +-----------+
| 1 | a | | 2 | k | | 2 | b | k |
| 2 | b | | 3 | m | | 3 | c | m |
| 3 | c | | 4 | n | +-----------+
+-------+ +-------+
CROSS JOIN
CROSS JOIN は、INNER JOIN と違って、結合条件を指定しません。
[左テーブル] CROSS JOIN [右テーブル] のように結合すると、[左テーブル] と [右テーブル]の両方のテーブルの、全てのコンビネーションのレコードの結果セットを取得することができます。
FROM A CROSS JOIN B
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
また、CROSS JOIN は、カンマを使用して暗黙的に記述できますので、上記例の中のFROM A CROSS JOIN B部分をFROM A, Bに書着かえても同じ結果になります。
FULL [OUTER] JOIN
FULL OUTER JOINまたはFULL JOIN を使って、 [左テーブル] FULL OUTER JOIN [右テーブル] ... のように結合すると、指定した条件にマッチする [左テーブル] と [右テーブル] のレコードに加えて、[左テーブル] のマッチしないレコードと、[右テーブル] のマッチしないレコードが取得できます。
1 つのテーブルの指定されたレコードが他のテーブルの任意のレコードに結合しない場合、レコードが他のテーブルからのすべての列に対して NULL を返します。
-- ON句を使用する場合
FROM A FULL OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
-- USING句を使用する場合
FROM A FULL OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
| 4 | NULL | p |
+--------------------+
LEFT [OUTER] JOIN
LEFT JOIN を使って、 [左テーブル] LEFT JOIN [右テーブル] ... のように結合すると、左側の [左テーブル] のすべてのレコードと、指定した条件にマッチする [右テーブル] のレコードが取得されます。
左側のテーブルのいずれかのレコードが右側のテーブルのどのレコードとも結合しない場合、レコードは右側のテーブルのすべての列に対して NULL を返します。左側のテーブルのどのレコードにも結合されない右側のテーブルのレコードは破棄されます。
-- ON句を使用する場合
FROM A LEFT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
+---------------------------+
-- USING句を使用する場合
FROM A LEFT OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
+--------------------+
RIGHT [OUTER] JOIN
RIGHT [OUTER] JOINの結果は、LEFT [OUTER] JOIN の結果と同様になり、対称的になります。
-- ON句を使用する場合
FROM A RIGHT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
-- USING句を使用する場合
FROM A RIGHT OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 2 | b | k |
| 2 | b | | 3 | m | | 3 | c | m |
| 3 | c | | 3 | n | | 3 | c | n |
| 3 | d | | 4 | p | | 3 | d | m |
+-------+ +-------+ | 3 | d | n |
| 4 | NULL | p |
+--------------------+
参考
Hakky では社内のデータ活用やサービスとしてデータ提供を行うためのソリューションを展開しております。
「BigQuery の管理や使い方に困っている」など具体的な相談はもちろんのこと、「どんなことをお願いできるのか知りたい」や「こんなことをやりたい」など、ご検討段階でも構いませんので、ぜひお気軽にフォームよりお問い合わせくださいませ。
