When a SQL unpivot turns twelve month columns into rows, what happens to NULL month cells, and how do you decide whether to keep them?
answer
- a NULL cell is still a selected value
- row count against source times columns
- which aggregates notice the row
- vendor operators choose differently
- blank is not the same as zero
basics
~20 sUNION ALL and LATERAL VALUES keep them as rows with a NULL amount, so the count stays source rows times columns. Drop them with an IS NOT NULL filter when a missing month carries no meaning you need.
solid answer
~50 sNeither portable form treats NULL specially: a `UNION ALL` branch or a `VALUES` row emits a row whatever the cell holds, so the count stays exactly source rows times twelve. To drop them, add `WHERE amount IS NOT NULL` around the stack or on the `LATERAL` result. Vendor operators differ: SQL Server's `UNPIVOT` always drops NULLs, and Oracle's drops them unless you write `INCLUDE NULLS`. I decide by what a blank means and what reads the table. `SUM` and `AVG` ignore NULLs, so keeping those rows does not move them, but `COUNT(*)` does count them. Dropping suits a fact table that holds only reported figures; keeping suits a table that must show every product-month. Dropping also removes a product whose months are all NULL. I only turn NULL into 0 if a blank really means zero, because that changes `AVG`.
code
sql · 17 linesWITH sales_wide (product_id, jan_sales, feb_sales, mar_sales) AS (
VALUES (1, 10, NULL, 30),
(2, 5, 7, NULL)
),
long_sales AS (
SELECT w.product_id, m.month_no, m.amount
FROM sales_wide AS w
CROSS JOIN LATERAL (
VALUES (1, w.jan_sales), (2, w.feb_sales), (3, w.mar_sales)
) AS m (month_no, amount)
)
SELECT COUNT(*) AS all_rows,
COUNT(amount) AS non_null_rows,
SUM(amount) AS total,
ROUND(AVG(amount), 2) AS avg_kept,
ROUND(AVG(COALESCE(amount, 0)), 2) AS avg_zeroed
FROM long_sales;go deeper
Recall that a NULL cell still becomes a row in the UNION ALL and VALUES forms, and that WHERE amount IS NOT NULL is how you remove those rows.
Walk through which aggregates change: SUM and AVG ignore NULLs, COUNT(*) does not. Then say how SQL Server's and Oracle's UNPIVOT operators differ from the hand-written forms.
Lead with what a blank means in the source, and ask who owns that answer. Point out the all-NULL product that vanishes, and how a totals check can miss a change in row count.
Set the rule for a whole warehouse: whether long tables store blanks, who confirms a blank's meaning per feed, and that zero is never imputed at load without an owner saying it is true.
## What the portable forms do An unpivot selects each cell as a value. It does not look at what the value is, so **NULL** is treated like anything else. - In the **`UNION ALL` form**, each branch returns one row per source row, and the column it selects may be NULL. - In the **`CROSS JOIN LATERAL (VALUES ...)` form**, each pair in the list makes a row, and a pair whose second value is NULL is still a row. So the row count is fixed in advance: source rows times unpivoted columns. Two products and three months give six rows, even when two of the cells are empty. ## What the vendor operators do SQL Server and Oracle ship an `UNPIVOT` operator, and they choose differently from the hand-written forms. The difference catches people out when they swap one form for another. | Form | NULL cell | Way to change it | |---|---|---| | `UNION ALL` branches | Row with NULL amount | Add `IS NOT NULL` filter | | `LATERAL (VALUES ...)` | Row with NULL amount | Add `IS NOT NULL` filter | | SQL Server `UNPIVOT` | No row | Use a hand-written form | | Oracle `UNPIVOT` | No row by default | Write `INCLUDE NULLS` | ## Which results notice the difference Keeping or dropping the row is not the same as changing the value. These are the effects on a report, using the inline example below, where four cells hold 10, 30, 5 and 7, and two are NULL: - **`SUM(amount)`** is 52 either way, because it adds only non-null inputs. - **`AVG(amount)`** is 13 either way, because it divides by the count of non-null inputs. - **`COUNT(amount)`** is 4 either way, for the same reason. - **`COUNT(*)`** is 6 if the rows are kept and 4 if they are dropped. - **`AVG(COALESCE(amount, 0))`** is about 8.67, because the two blanks now count as real zeros. The last line is the serious one. Converting NULL to 0 is a claim about the business. If a blank means "this product was not reported yet", then a zero says something false, and the average moves with it. ## Deciding what to do Ask two questions in order. 1. **What does a blank mean in the source?** It might mean no sales, not reported yet, or product not on sale that month. Only the first of these is a zero, and the source owner may be the only person who knows. 2. **What will read the long table?** Answer that with its consumers in mind: - A fact table of reported figures usually drops blanks, which saves rows and makes `COUNT(*)` mean "months reported". - A completeness report needs the blanks, because the missing months are the point. - A chart with a continuous time axis usually wants every month present, so a gap is shown as a gap. ## The product that disappears A filter works on rows, and it has no idea that several rows came from one product. If every month for a product is NULL, then dropping NULL rows leaves that product with no rows at all, and it vanishes from anything built on the long table. A later join can bring it back, if you start from the product list and outer-join to the long rows. Otherwise a count of products falls with no error, so check for this before you drop blanks. ## What a round trip can lose Unpivoting and then pivoting back with conditional aggregation looks like an exact inverse. It is close, and it is not exact: - **An all-NULL product is gone** if the blanks were dropped, because no row remains to group. - **The difference between NULL and 0 is gone** if blanks were turned into zeros along the way. - **Duplicate source rows collapse**, because a pivot groups by product and has to aggregate them. A `MAX(CASE WHEN month_no = 2 THEN amount END)` with no `ELSE` returns NULL for a month with no row, so a dropped cell comes back as NULL for any product that still has another row. That part of the round trip is safe, which is why the product-level loss is the one to look for.
- A team switches a report from a hand-written unpivot to SQL Server's UNPIVOT operator, and its monthly row counts fall. Why?SQL Server's `UNPIVOT` leaves out NULL cells, while the hand-written form returned them as rows with a NULL amount. `SUM` and `AVG` stay the same, since they ignore NULLs anyway, so only `COUNT(*)` and anything that expects every product-month changes. That is why the drop can pass a totals check and show up later as missing rows.
- After dropping NULL months, how do you keep a product whose months are all NULL in a report?Start from the product list and `LEFT JOIN` the long rows, so a product with no long rows still appears, with a NULL amount. For one row per product-month, cross join products with a list of months first, then left join the long rows to that. The long table then needn't store blanks, while the report still shows every product.
- Is a blank month ever safe to convert to zero?Yes, when the source owner confirms that a blank means none, such as a spreadsheet where people leave no-sale months empty. Then `COALESCE(cell, 0)` records that real meaning, and `AVG` properly counts the month. Without that confirmation, keep NULL: you can turn NULL into 0 in a later report, but once stored, a converted 0 cannot be told apart from a real one.
- UNION ALL skips NULL cells, so the unpivot only returns months with data.
- Keeping NULL rows distorts SUM and AVG, so they must always be dropped first.
- Replacing NULL with 0 is harmless tidying that does not change any aggregate.
- Every UNPIVOT operator treats NULLs the same way as the hand-written forms.
- Dropping NULL rows can never remove a product from the output.
- Pivoting an unpivoted table always gives back exactly the original table.