【pyspark・snowpark開発】データフレームのJoinにおけるhowの種類について

joinのhowの種類について

SQLやpyspark(snowpark)などのコードでデータフレーム(テーブル)の結合を実施して、テーブル間でデータ結合する機会があるかと思いますが、結合の種類について改めてサンプル例も含めて整理してみました。
実務ではよくleftのOuterJoinを使用するが、それ以外にも種類があったのでまとめてみました。

種類	how の値	説明	結果（例）
Inner Join	"inner"（デフォルト）	両方に存在する行だけ結合	id=2,3 のみ
Left Outer Join	"left", "leftouter"	左にある全行 + 右が一致すれば結合	id=1 は department=None
Right Outer Join	"right", "rightouter"	右にある全行 + 左が一致すれば結合	id=4 は name=None
Full Outer Join	"full", "outer", "fullouter"	両方の全行を対象、一致しない部分は None	id=1, id=4 も含まれる
Left Semi Join	"semi", "leftsemi"	左から「一致するidのみ」、右側は無視	id=2, id=3 のみ（右の列なし）
Left Anti Join	"anti", "leftanti"	左から「一致しないidのみ」、右側は無視	id=1 のみ（右の列なし）
Cross Join	"cross"	直積（すべての組み合わせ）	3行 × 3行 = 9行になる

サンプルテーブル

サンプルデータとして下記のテーブルを使用するものとする。
▼ 左のテーブル（employees）

id	name
1	Alice
2	Bob
3	Charlie

▼ 右のテーブル（departments）

id	department
2	HR
3	Engineering
4	Marketing

inner joinについて

inner joinの結果のテーブルの形式は下記のとおり。お互いの結合条件で一致する行のみが取得される。

id	name	id	department
2	Bob	2	HR
3	Charlie	3	Engineering

SQLでの実装方法は下記の通り。

innerjoin.sql

SELECT *
FROM employees e
INNER JOIN departments d
ON e.id = d.id;

snowparkでの実装方法は下記の通り。

innner.py

joined_df = employees.join(departments, employees["id"] == departments["id"], how="inner")
joined_df.select(
    employees["id"].alias("emp_id"),
    employees["name"],
    departments["department"]
).show()

left outer joinについて

　実際の実務でよく使われますが、左外部結合(left outer join)の結果は下記の通り。もともとの左側テーブルの情報は維持されて右側にjoinしたテーブルがくっつくイメージになります。結合条件にマッチしないレコードにはNULLがつく。

e.id	name	d.id	department
1	Alice	NULL	NULL
2	Bob	2	HR
3	Charlie	3	Engineering

SQLでの実装方法は下記の通り。

left.sql

SELECT e.id AS emp_id, e.name, d.department
FROM employees e
LEFT JOIN departments d
ON e.id = d.id;

snowparkでの実装方法は下記の通り。

left.py

joined_df = employees.join(departments, employees["id"] == departments["id"], how="left")
joined_df.select(
    employees["id"].alias("emp_id"),
    employees["name"],
    departments["department"]
).show()

full outer joinについて

　実務ではfull outer joinはあまり見かけないですが

e.id	name	d.id	department
1	Alice	NULL	NULL
2	Bob	2	HR
3	Charlie	3	Engineering
NULL	NULL	4	Marketing

SQLでの実装方法は下記の通り。

fullouter.sql

SELECT e.id, e.name, d.department
FROM employees e
FULL OUTER JOIN departments d ON e.id = d.id;

snowparkでの実装方法は下記の通り。

outer.py

joined_df = employees.join(departments, employees["id"] == departments["id"], how="fullouter")
joined_df.select(
    employees["id"].alias("emp_id"),
    employees["name"],
    departments["department"]
).show()

Cross Joinについて

　クロス結合もあまり見かけませんが、出力テーブルの結果は下記のとおりです。お互いのレコード数の組み合わせ (ｎ × ｎのレコード)ができるイメージになります。

e.id	name	d.id	department
1	Alice	2	HR
1	Alice	3	Engineering
1	Alice	4	Marketing
2	Bob	2	HR
2	Bob	3	Engineering
2	Bob	4	Marketing
3	Charlie	2	HR
3	Charlie	3	Engineering
3	Charlie	4	Marketing

SQLでの実装方法は下記の通り。

cross.sql

SELECT e.id AS emp_id, e.name, d.department
FROM employees e
CROSS JOIN departments d;

snowparkでの実装方法は下記の通り。

cross.py

joined_df = employees.join(departments, how="cross")
joined_df.select(
    employees["id"].alias("emp_id"),
    employees["name"],
    departments["department"]
).show()

Semi JoinとAnti Joinについて

　上記のleft JoinやFull Outer Join以外にも、下記のセミ結合やアンチ結合の結合方式もあるので頭の片隅に入れておくとよいかもしれないです。

LeftSemi.sql

SELECT e.id, e.name
FROM employees e
WHERE e.id IN (SELECT id FROM departments);

id	name
2	Bob
3	Charlie

LeftAnti.sql

SELECT e.id, e.name
FROM employees e
WHERE e.id NOT IN (SELECT id FROM departments);

id	name
1	Alice

最後に

　実際のコーディングでは、元々取り扱っているデータフレーム(左側テーブル)に対して、条件に一致するデータフレームも右側に追記していく形をよく見ます。それ以外のjoin結合についても頭に入れておきましょう。

left.py

joined_df = employees.join(departments, employees["id"] == departments["id"], how="left")
joined_df.select(
   employees["id"].alias("emp_id"),
   employees["name"],
   departments["department"]
).show()