LEFT JOIN の右側のテーブルに対する条件指定はWHERE句ではなくON句に書こう

# 概要
SQLでテーブル同士を LEFT JOIN する際に、右側のテーブルに対して条件指定を行うクエリの書き方を紹介します。
(例えば、行数の多い巨大なログテーブル同士の LEFT JOIN で、双方のテーブルに対して partition の絞り込みをしたい、といったケースが想定されます)
この時、条件を WHERE 句に書いてしまうと INNER JOIN と同じ挙動になってしまうので、条件は ON 句に書く必要があるよ、というのが本記事の主旨になります。

実行環境は BigQuery を想定しています。

# やりたいこと
下記 `log_a` , `log_b` の2テーブルを使います。
それぞれ、 日付を表す`dt`カラムでパーティションが切られたログのテーブルです。日付以外には、ユーザー識別子の `user_id` と 行動内容を表すカラムがそれぞれ `action`, `another_action`に格納されているとします。

**log_a**

| dt | user_id | action |
|:-:|:-:|:-:|
| 2021-01-01  | 1 | A |
| 2021-01-01  | 2 | B |
| 2021-01-02  | 3 | C |

**log_b**

| dt | user_id | another_action |
|:-:|:-:|:-:|
| 2021-01-01  | 1  | a  |
| 2021-01-02  | 3 | c |

ここで、 **2021-01-01 の日付のログに対して、`log_a` に存在するユーザーが`log_b`のログでどんなアクションをしたか** 紐づけようと思います。
つまり、下記のようなデータを抽出することがゴールになります。

| dt | user_id | action | another_action |
|:-:|:-:|:-:|:-:|
| 2021-01-01  | 1 | A | a |
| 2021-01-01  | 2 | B |NULL|


# テーブル定義
集計で使用するログテーブルを下記の通り定義します。

```sql
/* log_a CREATE and INSERT */
CREATE OR REPLACE TABLE
 qiita.log_a
(
 dt DATE,
 user_id INT64,
 action STRING
)
PARTITION BY dt
;

INSERT qiita.log_a (dt, user_id, action)
VALUES 
(DATE("2021-01-01"), 1, "A"),
(DATE("2021-01-01"), 2, "B"),
(DATE("2021-01-02"), 3, "C")
;
```

```sql
/* log_b CREATE and INSERT */
CREATE OR REPLACE TABLE
 qiita.log_b
(
 dt DATE,
 user_id INT64,
 another_action STRING
)
PARTITION BY dt
;

INSERT qiita.log_b (dt, user_id, another_action)
VALUES 
(DATE("2021-01-01"), 1, "a"),
(DATE("2021-01-02"), 3, "c")
;
```

# NG例： WHERE句に条件を書く
それでは、`log_a`, `log_b` を使って 2021-01-01 に絞り、`log_a` に `log_b` を LEFT JOIN してみましょう。

```sql
/* NG例
LEFT JOIN の右側のテーブルに対する条件指定をWHERE句に書いた場合 */
SELECT
 a.dt,
 a.user_id,
 a.action,
 b.another_action
FROM
 qiita.log_a as a
LEFT JOIN
 qiita.log_b as b
ON
 a.user_id=b.user_id
WHERE
 a.dt = "2021-01-01"
 and b.dt = "2021-01-01"  /* bに対する条件指定をWHERE句に記述 */
```
このクエリの実行結果は下記の通りです。
LEFT JOIN を使っているのに、 a, b 双方存在するレコードしか出力されません。
何が起こっているのでしょうか？

| dt | user_id | action | another_action |
|:-:|:-:|:-:|:-:|
| 2021-01-01 | 1 | A | a |

## 何が起こっているのか？
↑ の書き方では何故ダメなのか調べるために、一度 WHERE 句に対する条件指定を消して再度実行してみます。

```sql
/* NG例 の書き方が何故ダメなのか調査
単に LEFT JOIN した全レコードを出力する */
SELECT
 a.dt as a_dt,
 b.dt as b_dt,
 a.user_id as a_user_id,
 b.user_id as b_user_id,
 a.action,
 b.another_action
FROM
 qiita.log_a as a
LEFT JOIN
 qiita.log_b as b
ON
 a.user_id=b.user_id
```

結果は下記の通りです。

| a_dt | b_dt | a_user_id | b_user_id | action | another_action |
|:-:|:-:|:-:|:-:|:-:|:-:|
| 2021-01-01 | 2021-01-01 | 1 | 1 | A | a |
| 2021-01-01 | NULL | 2 | NULL | B | NULL |
| 2021-01-02 | 2021-01-02 | 3 | 3 | C | c |

`log_b` の方には存在しない user_id=2 のレコード (2行目) に注目すると、
**`log_b` 由来のカラムは全て NULL になっている**ことがわかります。
従って、NG例のように WHERE句で `b.dt = "2021-01-01"` と指定しても
2行目は TRUE にならないため結果として出力されません。


## 正しい書き方：ON句(結合条件)に書く
これを回避するために、 右側のテーブルに対する条件指定は ON句に書きましょう。

```sql
/* LEFT JOIN の右側のテーブルに対する条件指定をON句(結合条件)に書いた場合 */
SELECT
 a.dt,
 a.user_id,
 a.action,
 b.another_action
FROM
 qiita.log_a as a
LEFT JOIN
 qiita.log_b as b
ON
 a.user_id=b.user_id
 and b.dt = "2021-01-01"  /* bに対する条件指定をON句に記述 */
WHERE
 a.dt = "2021-01-01"
```

結果は ↓ の通りで、所望の結果を得ることができました。

| dt | user_id | action | another_action |
|:-:|:-:|:-:|:-:|
| 2021-01-01  | 1 | A | a |
| 2021-01-01  | 2 | B |NULL|