概要

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

テーブル定義

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

/* 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")
;

/* 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 してみましょう。

/* 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 句に対する条件指定を消して再度実行してみます。

/* 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句に書きましょう。

/* 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