Datawise
Practical SQL

Using INCLUDE NULLS with UNPIVOT in BigQuery

Constantin LunguUpdated 1 min read

Photo by Pierre Bamin on Unsplash

While solving a bug I was reminded again that, when UNPIVOTing, rows with NULL values are excluded. Fine.

But it turns out we have the option to specify the INCLUDE NULLS with UNPIVOT, thus allowing us to keep those rows in the result set.

Let's look at an example.

BigQuery console result showing the wide input table for UNPIVOT, with columns measurement_date, water_level, temperature and pressure for 2021-01-01 to 2021-01-03, a null water_level on 2021-01-01 and a null temperature on 2021-01-02.

This is how it would look if UNPIVOTed as usual:

SELECT 
    measurement_date, 
    value, 
    measurement 
FROM input
UNPIVOT INCLUDE NULLS (value FOR measurement IN (water_level, temperature, pressure))

BigQuery console result of a regular UNPIVOT into columns measurement_date, value and measurement: only 7 rows, because the null water_level for 2021-01-01 and the null temperature for 2021-01-02 are dropped.

As you notice, we don't have the rows where the measurement values are NULL.

How can we fix it? Let's use UNPIVOT in conjunction with INCLUDE NULLS.

SELECT 
    measurement_date, 
    value, 
    measurement 
FROM input
UNPIVOT INCLUDE NULLS (value FOR measurement IN (water_level, temperature, pressure))

BigQuery console result of UNPIVOT INCLUDE NULLS with columns measurement_date, value and measurement: all 9 rows appear, including null values for water_level on 2021-01-01 and temperature on 2021-01-02.

Voila! The NULL entries are here now.