2023-06-25

Finding (null) Value in array

The standard way to check if a value is in an array in BigQuery is using IN UNNEST(...). For example:

SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;

What is the suggested way to check if the NULL value is in an array? Is there a more direct way than doing:

SELECT EXISTS(SELECT 1 FROM UNNEST([0,1,1,2,3,null,5]) v WHERE v IS NULL)

Is there a more straightforward way for this? And if someone on the BigQuery team or someone familiar with performance/optimizations: does it matter if I add a LIMIT 1 to the sub-select -- i.e., does the exists operator short-circuit automatically once it finds a result or do I have to manually add in a LIMIT 1 to the subquery?



No comments:

Post a Comment