BigQueryでUNNESTをLEFT JOINすると空のARRAYでも行が残る

BigQueryで こういう Nest したレコードがあったとします。

WITH a as (
SELECT * 
FROM UNNEST([
  STRUCT(1 as id, [1, 2] as data),
  STRUCT(2 as id, [] as data),
  STRUCT(3 as id, NULL as data)
  ])
)

SELECT * FROM a

行	id	data
1	1	1
		2
2	2	(0 rows)
3	3	(0 rows)

---

普通に?(よくサンプルにあるように)
FROM a, UNNEST(data) as data という形で UNNESTすると、
レコード数が0 だったりNULLだったりすると、行そのものが消えます。

WITH a as (
SELECT * 
FROM UNNEST([
  STRUCT(1 as id, [1, 2] as data),
  STRUCT(2 as id, [] as data),
  STRUCT(3 as id, NULL as data)
  ])
)

SELECT
  id,
  data
FROM a, UNNEST(data) as data

行	id	data
1	1	1
2	1	2

これはこれで嬉しいときもありますが、残したい時もあります。

---

そういうときは、LEFT JOIN UNNEST(data) as data とすると残るのです。

WITH a as (
SELECT * 
FROM UNNEST([
  STRUCT(1 as id, [1, 2] as data),
  STRUCT(2 as id, [] as data),
  STRUCT(3 as id, NULL as data)
  ])
)

SELECT
  id,
  data
FROM a
LEFT JOIN UNNEST(data) as data

行	id	data
1	1	1
2	1	2
3	2	null
4	3	null

知らなかった...