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...

Solution

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;

Comments

[Willian Fuks]

Hi Elliott, looks like the keyword unnest is reserved, got the message "Unexpected keyword UNNEST". Maybe it needs to be enclosed by the ` sign or be called "unnest_".

[柳沼慎哉]

Thank you, Elliott !! I just do as you say, and I could get a result which I want.

[Long Le]

@ElliottBrossard Sorry, I still don't understand why LEFT JOIN can keep NULL values from UNNEST Tables but CROSS JOIN doesn't. If there is a null value in the UNNEST table then there should still be null values right?

[Elliott Brossard]

It's the same as if you left join t1 and t2, where t1 is empty and t2 has one. Unnesting an empty or null array returns a table with no rows.

[Vikash Pareek]

@ElliottBrossard, in a left join it always requires a joining condition. but here in UNNEST function we are not giving any join condition, still it returns the results. Can explain what is the default join condition UNNEST function is using internally?

[Elliott Brossard]

I no longer work on BigQuery (I'm with Snowflake now).