SQL の LEFT JOIN の挙動についての調査メモ

✏️ 編集

この前、 BigQuery で LEFT JOIN するクエリを書いていた時に少しつまづいたので、その時の調査結果を備忘録として残す（おそらく BigQuery に限らず SQL 全般の話だと思うので、記事タイトルは「SQL の」としている）。

前提: 対象データ

次のような 3 つのテーブルを考える。

LEFT JOIN してみる ①

この 3 つのテーブルを JOIN して、 (item_key, item_code, shop_id) の組み合わせテーブルを作ることを考える。

今回は次の 3 パターンの LEFT JOIN で実現してみる。

次の順で LEFT JOIN する: ItemShops → Shops
次の順で LEFT JOIN する: Items → ItemShops → Shops
次の順で LEFT JOIN する: ItemShops → Shops → Items

パターン 1: ItemShops → Shops

クエリ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH

Items AS (
  SELECT 'xxx' AS item_key, 'aaa' AS item_code
  UNION ALL
  SELECT 'yyy', 'bbb'
),

ItemShops AS (
  SELECT 'aaa' AS item_code, 1 AS shop_id
  UNION ALL
  SELECT 'aaa', 2
  UNION ALL
  SELECT 'bbb', 1
  UNION ALL
  SELECT 'bbb', 2
),

Shops AS (
  SELECT 1 AS shop_id, 'xxx' AS item_key
  UNION ALL
  SELECT 2, 'yyy'
)

SELECT
  C.item_key,
  B.item_code,
  B.shop_id
FROM
  ItemShops B
LEFT JOIN
  Shops C USING(shop_id)

結果:

(item_key, item_code) の組み合わせがおかしい行が出てしまうため、この JOIN 方法では実現できない。

パターン 2: Items → ItemShops → Shops

クエリ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH

Items AS (
  SELECT 'xxx' AS item_key, 'aaa' AS item_code
  UNION ALL
  SELECT 'yyy', 'bbb'
),

ItemShops AS (
  SELECT 'aaa' AS item_code, 1 AS shop_id
  UNION ALL
  SELECT 'aaa', 2
  UNION ALL
  SELECT 'bbb', 1
  UNION ALL
  SELECT 'bbb', 2
),

Shops AS (
  SELECT 1 AS shop_id, 'xxx' AS item_key
  UNION ALL
  SELECT 2, 'yyy'
)

SELECT
  A.item_key AS A_item_key,
  C.item_key AS C_item_key,
  B.item_code,
  B.shop_id,
FROM
  Items A
LEFT JOIN
  ItemShops B USING(item_code)
LEFT JOIN
  Shops C USING(shop_id)

結果:

A_item_key （＝ Items.item_key ）を採用しないと対応がおかしくなる。

パターン 3: ItemShops → Shops → Items

クエリ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH

Items AS (
  SELECT 'xxx' AS item_key, 'aaa' AS item_code
  UNION ALL
  SELECT 'yyy', 'bbb'
),

ItemShops AS (
  SELECT 'aaa' AS item_code, 1 AS shop_id
  UNION ALL
  SELECT 'aaa', 2
  UNION ALL
  SELECT 'bbb', 1
  UNION ALL
  SELECT 'bbb', 2
),

Shops AS (
  SELECT 1 AS shop_id, 'xxx' AS item_key
  UNION ALL
  SELECT 2, 'yyy'
)

SELECT
  A.item_key AS A_item_key,
  C.item_key AS C_item_key,
  B.item_code,
  B.shop_id,
FROM
  ItemShops B
LEFT JOIN
  Shops C USING(shop_id)
LEFT JOIN
  Items A USING(item_code)

結果:

パターン 2 と同じ。

LEFT JOIN してみる ②

何らかの理由で Items テーブルに存在しない item_key が Shops テーブルに組まれている状況を考える。

Shops.item_key = "yyy" は Items テーブルに存在しない。

この状況で、先ほどと同じ 3 パターンの LEFT JOIN を行ってみる。

パターン 1: ItemShops → Shops

クエリ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH

Items AS (
  SELECT 'xxx' AS item_key, 'aaa' AS item_code
),

ItemShops AS (
  SELECT 'aaa' AS item_code, 1 AS shop_id
  UNION ALL
  SELECT 'aaa', 2
  UNION ALL
  SELECT 'bbb', 1
  UNION ALL
  SELECT 'bbb', 2
),

Shops AS (
  SELECT 1 AS shop_id, 'xxx' AS item_key
  UNION ALL
  SELECT 2, 'yyy'
)

SELECT
  C.item_key,
  B.item_code,
  B.shop_id
FROM
  ItemShops B
LEFT JOIN
  Shops C USING(shop_id)

結果:

存在しないはずの item_key = "yyy" の行が残っていたり、 (item_key, item_code) の組み合わせがおかしい行が存在していたりしており、適切な JOIN 方法ではないことがわかる。

パターン 2: Items → ItemShops → Shops

クエリ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
WITH

Items AS (
  SELECT 'xxx' AS item_key, 'aaa' AS item_code
),

ItemShops AS (
  SELECT 'aaa' AS item_code, 1 AS shop_id
  UNION ALL
  SELECT 'aaa', 2
  UNION ALL
  SELECT 'bbb', 1
  UNION ALL
  SELECT 'bbb', 2
),

Shops AS (
  SELECT 1 AS shop_id, 'xxx' AS item_key
  UNION ALL
  SELECT 2, 'yyy'
)

SELECT
  A.item_key AS A_item_key,
  C.item_key AS C_item_key,
  B.item_code,
  B.shop_id,
FROM
  Items A
LEFT JOIN
  ItemShops B USING(item_code)
LEFT JOIN
  Shops C USING(shop_id)

結果:

A_item_key （＝ Items.item_key ）の方を採用すれば、この JOIN 方法で OK 。

パターン 3: ItemShops → Shops → Items

クエリ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
WITH

Items AS (
  SELECT 'xxx' AS item_key, 'aaa' AS item_code
),

ItemShops AS (
  SELECT 'aaa' AS item_code, 1 AS shop_id
  UNION ALL
  SELECT 'aaa', 2
  UNION ALL
  SELECT 'bbb', 1
  UNION ALL
  SELECT 'bbb', 2
),

Shops AS (
  SELECT 1 AS shop_id, 'xxx' AS item_key
  UNION ALL
  SELECT 2, 'yyy'
)

SELECT
  A.item_key AS A_item_key,
  C.item_key AS C_item_key,
  B.item_code,
  B.shop_id,
FROM
  ItemShops B
LEFT JOIN
  Shops C USING(shop_id)
LEFT JOIN
  Items A USING(item_code)