In BigQuery, why does FROM t, UNNEST(t.items) AS item drop rows whose items array is empty?
answer
- the comma is not free notation
- one row times zero elements
- outer joins keep the childless parent
- a WHERE on the outer side undoes it
- ARRAY_LENGTH(items) = 0 names the missing rows
basics
~20 sThe comma is a CROSS JOIN, and cross-joining a row against zero elements produces no output rows, so parents with empty arrays disappear. Use LEFT JOIN UNNEST(t.items) AS item to keep them, with the element columns NULL.
solid answer
~50 sIn GoogleSQL the comma in `FROM` is a `CROSS JOIN`, and `UNNEST` correlated to the row is joined against that row's own array. An order with three line items produces three rows; an order with an empty array produces zero, so it silently vanishes from the result. This is the classic BigQuery reporting bug: totals look plausible but every parent with no children is missing, and nobody notices until someone reconciles counts. The fix is an outer join — `FROM orders AS o LEFT JOIN UNNEST(o.items) AS item` — which emits the parent row once with every `item.*` column NULL. Beware the second half of the trap: adding a predicate on `item.sku` in `WHERE` turns the outer join back into an inner one and drops those rows again. Filter inside the array instead, or use `EXISTS (SELECT 1 FROM UNNEST(o.items) ...)`.
go deeper
Know that the comma before UNNEST is a cross join, and that a row whose repeated field is empty produces no output rows at all unless you use LEFT JOIN UNNEST.
Explain the multiplication that makes zero elements yield zero rows, write the outer-join fix, and describe how a predicate on the flattened alias silently reverses it.
Demonstrate how you would catch this in review or in production: reconcile parent row counts against the flattened count, and prefer array subqueries or EXISTS when the question is asked at parent granularity.
Own the guardrails — certified flattened views over nested source tables, count reconciliation in the transformation tests, and a house rule for how analysts query event exports.
## What the comma actually means In BigQuery's GoogleSQL dialect, a comma between `FROM` items is a `CROSS JOIN`. Written against a correlated `UNNEST`, the join is evaluated per row against that row's own array: ```sql SELECT o.order_id, item.sku FROM orders AS o, UNNEST(o.items) AS item; ``` A cross join multiplies: one parent row times N array elements gives N output rows. The multiplication is the point when N is 3. The problem is that the same arithmetic applies when N is 0. One row times zero elements is zero rows, and the order disappears — no error, no warning, just a smaller result. This matters more in BigQuery than the equivalent trap elsewhere because a repeated field is *empty*, not NULL, whenever the child collection has no members. Orders with no line items, sessions with no events, users with no purchases: every parent whose sub-table happens to be empty is exactly the population that vanishes. And these rows are often the interesting ones — the abandoned carts, the bounced sessions. ## The symptom in the wild ```sql -- "How many orders did we take yesterday?" SELECT COUNT(DISTINCT o.order_id) FROM orders AS o, UNNEST(o.items) AS item WHERE o.order_date = CURRENT_DATE() - 1; ``` This does not count orders. It counts orders *that have at least one line item*. The number is close enough to look right, which is why the bug survives review. The same shape hides inside a funnel query built on nested event data: any user whose event array is empty is not counted as having failed the step; they are not counted at all. ## The fix: outer join the array ```sql SELECT o.order_id, item.sku FROM orders AS o LEFT JOIN UNNEST(o.items) AS item; ``` The outer join preserves the parent exactly once when the array is empty, with every field of `item` NULL. All the usual outer-join reasoning then applies: `COUNT(item.sku)` counts only real elements, while `COUNT(*)` counts the placeholder row too. ## The second half of the trap The fix is routinely undone one line later: ```sql SELECT o.order_id FROM orders AS o LEFT JOIN UNNEST(o.items) AS item WHERE item.sku LIKE 'BOOK-%'; -- kills the outer join ``` A `WHERE` predicate on a nullable column from the outer side rejects the NULL placeholder row, so the empty-array parents are dropped again. There are two clean ways out. **Filter inside the array before flattening**, so the array is possibly empty but the parent still survives: ```sql SELECT o.order_id, item.sku FROM orders AS o LEFT JOIN UNNEST(ARRAY(SELECT i FROM UNNEST(o.items) AS i WHERE i.sku LIKE 'BOOK-%')) AS item; ``` **Or don't flatten at all** when the question is about the parent. `EXISTS` and array subqueries answer per-parent questions at parent granularity: ```sql SELECT o.order_id, (SELECT COUNT(*) FROM UNNEST(o.items) AS i WHERE i.sku LIKE 'BOOK-%') AS book_lines FROM orders AS o; ``` Here the empty-array rows come back with `book_lines = 0`, which is the answer the analyst wanted in the first place. ## Diagnosing it The cheap check is a row-count reconciliation: `SELECT COUNT(*) FROM orders` against `SELECT COUNT(DISTINCT order_id) FROM orders, UNNEST(items)`. Any gap is the empty-array population, and `SELECT COUNT(*) FROM orders WHERE ARRAY_LENGTH(items) = 0` names it exactly. Note that `items IS NULL` is not the right test for the general case — the reliable predicate for "no children" on a repeated field is `ARRAY_LENGTH(items) = 0`. ## Why this is not just a BigQuery quirk The cross-join-with-lateral semantics are standard; the reason it bites so often here is that nested and repeated modelling is the *default* way BigQuery data arrives — Google Analytics exports, Firebase events, ad-platform transfers all ship deeply nested arrays. Anyone querying those tables meets this on day one, which is precisely why interviewers ask it.
- After switching to LEFT JOIN UNNEST, what is the difference between COUNT(*) and COUNT(item.sku)?`COUNT(*)` counts the placeholder row emitted for a parent whose array is empty, so it overstates the number of real elements by one per childless parent. `COUNT(item.sku)` skips NULLs and therefore counts only genuine array elements. If you need both, compute the parent count separately or use `COUNTIF(item.sku IS NOT NULL)` for clarity.
- How do you keep parents with empty arrays while still filtering the elements you flatten?Filter inside the array rather than in the outer WHERE clause: `LEFT JOIN UNNEST(ARRAY(SELECT i FROM UNNEST(o.items) AS i WHERE i.sku LIKE 'BOOK-%')) AS item`. The inner array may come back empty, and the outer join still emits the parent once with NULL element columns. A predicate on `item.sku` in WHERE would instead convert the outer join back to an inner one.
- Is there a cost penalty for using LEFT JOIN UNNEST instead of the comma form?No meaningful one. Bytes scanned depend on which leaf columns the query references, and both forms reference the same ones. Flattening is a reshaping step inside the query; the outer variant just emits one extra placeholder row per childless parent. Choose the form that gives the correct answer, not the one you think is cheaper.
saying these in an interview costs you the question
- Thinks the comma form and LEFT JOIN UNNEST return the same rows
- Blames missing rows on a partition filter rather than the flatten
- Adds a WHERE on the element column and calls the outer join fixed
- Tests for missing children with items IS NULL
- Claims UNNEST cannot be outer joined in BigQuery