Google BigQuery, I lost null row when using 'unnest' function

Question

#StandardSQL
WITH tableA AS (
SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_id
UNION ALL
SELECT ["T008", "T009"] AS T_id, NULL AS L_id
)

SELECT * FROM tableA, UNNEST(L_id) AS unnest

When I executed this code, I expected the result such as that below.

RowNumber  T-id            L-id  unnest
1          T001,T002,T003  1,5   1
2          T001,T002,T003  1,5   5
3          T004,T005       NULL  NULL

But I get this result instead:

RowNumber  T-id            L-id  unnest
1          T001,T002,T003  1,5   1
2          T001,T002,T003  1,5   5

I lost the third row. Then, I saw the official Google documentation, which states the following:

UNNEST treats NULL as follows.
 ・NULL and empty ARRAY generate zero rows.
 ・An ARRAY containing NULL generates a row containing a NULL value.

But I don't want to lose my null row.

How can I keep the null row?

Please tell me the solution...

Answer 1

Instead of CROSS JOIN, use LEFT JOIN. This will return a row with nulls for an empty array. You may also be interested in the working with arrays topic from the documentation.

#StandardSQL
WITH tableA AS (
  SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_id
  UNION ALL
  SELECT ["T008", "T009"] AS T_id, NULL AS L_id
)
SELECT * FROM tableA
LEFT JOIN UNNEST(L_id) AS value;