skip to content

questions

12

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?

level: middleimportance: must knowfreq 36%

answer

  1. a NULL cell is still a selected value
  2. row count against source times columns
  3. which aggregates notice the row
  4. vendor operators choose differently
  5. blank is not the same as zero

basics

~20 s

UNION 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 s

Neither 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 lines
sql
WITH 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

for a junior

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.

for a middle

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.

for a senior

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.

for a principal

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.
open as a page

In SQL, why can a bind parameter in a prepared statement stand for a value but not for a table or column name?

level: seniorimportance: must knowfreq 42%

basics

~20 s

The server must parse a statement and resolve its names before any value exists, because names decide its meaning, columns and types. A parameter supplies only a value at run time, so a name must go in the text.

open as a page

In SQL, what is dynamic SQL, and how do you tell when a static statement with bind parameters is already enough?

level: juniorimportance: should knowfreq 34%

basics

~20 s

Dynamic SQL is statement text assembled at run time and then sent to the server. A static statement with bind parameters is enough whenever only values change, not the tables, columns, clauses or sort order.

open as a page

In SQL, how do you unpivot a table with one row per product and twelve month columns into one row per product-month using UNION ALL?

level: juniorimportance: should knowfreq 38%

basics

~20 s

Write one SELECT per month column, each returning the product key, a month label and that column under a shared alias, and stack them with UNION ALL. Twelve branches give exactly twelve output rows per source row.

open as a page

In SQL, what do the PREPARE and EXECUTE statements do, and how do their forms differ between PostgreSQL and MySQL?

level: middleimportance: should knowfreq 38%

basics

~10 s

PREPARE stores a named statement with placeholders on the current session, and EXECUTE runs it with values. PostgreSQL writes PREPARE name AS statement with $1; MySQL writes PREPARE name FROM a string with ?.

open as a page

In SQL, what does double-quoting a table or column name chosen at run time guarantee, and what does it leave for an allowlist to decide?

level: middleimportance: should knowfreq 29%

basics

~20 s

Double quotes make text read as exactly one name, preserving case and escaping embedded quotes. They do not check that the name exists or that this caller may use it; an allowlist of names held in code decides that.

open as a page

Why must a portable SQL unpivot name its month columns when the query is written, and do vendor UNPIVOT operators remove that limit?

level: middleimportance: should knowfreq 24%

basics

~20 s

SQL resolves every column name when the statement is analysed, and no portable syntax treats column names as data, so the list is written by hand. SQL Server's and Oracle's UNPIVOT operators still take an explicit column list.

open as a page

In SQL, how does CROSS JOIN LATERAL (VALUES ...) unpivot twelve month columns, and what does it save compared with twelve UNION ALL branches?

level: middleimportance: should knowfreq 31%

basics

~20 s

For each source row, a VALUES list reads that row's columns and emits one label-and-value row per month. The result matches UNION ALL, but the source appears once, so it is typically read once rather than twelve times.

open as a page

In SQL, a multi-tenant admin report has a dozen optional filters, some taking lists; how do you build the statement, and why not one catch-all predicate?

level: seniorimportance: should knowfreq 36%

basics

~20 s

Start from the fixed tenant predicate and append one bound predicate per filter present, expanding lists into placeholders or an array. A catch-all such as ($2 IS NULL OR col = $2) can waste indexes and cannot ask for NULL.

open as a page

A SQL unpivot of spreadsheet-loaded month columns fails on some engines and gives wrong totals on others because some columns are text and some numeric; what is happening, and how do you fix it?

level: seniorimportance: should knowfreq 27%

basics

~20 s

An unpivot stacks many columns into one, which needs one type, and engines reconcile mismatches differently: some reject, some fail at run time, some convert silently. Fix it by cleaning and casting every listed column to one chosen type.

open as a page

A SQL report over a UNION ALL unpivot, with WHERE region = 'EU' after the last branch, shows every region for January to November and only EU for December; what went wrong, and how do you verify an unpivot?

level: seniorimportance: nice to knowfreq 22%

basics

~20 s

A WHERE clause belongs to one SELECT, so after the last branch it filters December only. Filter around the stack or use LATERAL VALUES, then verify with row counts and per-month totals against the source.

open as a page

As a lead, when would you allow dynamic SQL in a codebase at all, and what standing rules would you set around it?

level: principalimportance: nice to knowfreq 21%

basics

~20 s

Allow dynamic SQL only where the statement's shape truly varies and fixed statements cannot cover it, after asking whether the data model causes the variation. Then require one shared builder, fixed fragments, allowlisted names and bound values.

open as a page