【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()
```