Sets, Pivots & Reshaping Results

A query can give the right numbers in the wrong shape. A chart may need one row per status. A slide may need one column per status.

Reshaping means changing the shape of a result. In this chapter, you learn to:

1. Stack two results with UNION and UNION ALL.
2. Find shared rows and missing rows with INTERSECT and EXCEPT.
3. Turn rows into columns, and columns back into rows.
4. Add subtotal rows and a total row with ROLLUP.

The examples use the shop from chapter 1. These are the two tables you need:

Two NULL values matter in this chapter. Dev has no country, and order 105 has no amount.

The examples use PostgreSQL. Each section names the databases that work in a different way.

A set operator joins the results of two queries into one result. UNION and UNION ALL put the second result under the first.

We use two small lists of customers. List A has the customers of paid orders. List B has the customers of orders from 11 January on:

First, UNION ALL. It keeps every row. A text value in each query tells you which list a row came from:

SELECT 'A' AS list, customer_id
FROM orders
WHERE status = 'paid'
UNION ALL
SELECT 'B', customer_id
FROM orders
WHERE order_date >= '2026-01-11'
ORDER BY list, customer_id;

Now the same two lists with UNION. The label is gone, so equal rows can match:

SELECT customer_id
FROM orders
WHERE status = 'paid'
UNION
SELECT customer_id
FROM orders
WHERE order_date >= '2026-01-11'
ORDER BY customer_id;

UNION removes duplicates inside one list too, not only between the lists. Two real orders from Asha became one row.

To find duplicates, the database must compare all the rows, and that costs extra work. Use UNION ALL unless you want duplicates removed.

Rules for set operators

• Both queries must have the same number of columns.
• The columns match by position, not by name. The first column meets the first column.
• The types must fit each other. PostgreSQL gives an error for integer and text. Some databases change the type without an error.
• The result takes its column names from the first query.
• Write ORDER BY once, at the very end. It sorts the whole result.

The next query breaks the first rule. The first query has one column and the second has two:

SELECT customer_id FROM orders
UNION ALL
SELECT customer_id, name FROM customers;
-- ERROR: each UNION query must have the same number of columns

INTERSECT keeps the rows that are in both results. EXCEPT keeps the rows that are in the first result and not in the second.

Both compare whole rows. Both remove duplicates. We use lists A and B from the last section.

Customers 3 and 4 are in both lists. INTERSECT finds them:

SELECT customer_id
FROM orders
WHERE status = 'paid'
INTERSECT
SELECT customer_id
FROM orders
WHERE order_date >= '2026-01-11'
ORDER BY customer_id;

Now EXCEPT. It keeps the customers of list A that are not in list B:

SELECT customer_id
FROM orders
WHERE status = 'paid'
EXCEPT
SELECT customer_id
FROM orders
WHERE order_date >= '2026-01-11';

The order matters. Swap the two queries and you answer a different question:

SELECT customer_id
FROM orders
WHERE order_date >= '2026-01-11'
EXCEPT
SELECT customer_id
FROM orders
WHERE status = 'paid';

Keep duplicates with ALL

EXCEPT ALL and INTERSECT ALL keep duplicates. EXCEPT ALL removes one matching row for each row in the second list.

SELECT customer_id
FROM orders
WHERE status = 'paid'
EXCEPT ALL
SELECT customer_id
FROM orders
WHERE order_date >= '2026-01-11';

PostgreSQL, MySQL 8.0.31 and later, Oracle 21c and later, and Databricks have the ALL versions. SQL Server, Snowflake, BigQuery and SQLite do not.

Older Oracle versions call EXCEPT by the name MINUS, and Oracle 21c and later accept both. BigQuery needs you to write EXCEPT DISTINCT or INTERSECT DISTINCT.

How NULL behaves

Chapter 1 taught that NULL = NULL is not true. Set operators are different. They treat two NULL values as the same value.

Look at the country of each customer in the two lists. Dev has no country, so NULL is in both lists:

SELECT c.country
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
INTERSECT
SELECT c.country
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-11'
ORDER BY country;

A join with ON a.country = b.country would lose Dev, because NULL = NULL is unknown. NOT IN has a worse problem. Chapter 1 showed that NOT IN returns no rows when its list has a NULL.

EXCEPT and NOT EXISTS avoid that problem, but they differ on NULL. On the country lists, A EXCEPT B gives only IN. NOT EXISTS with = gives IN and NULL, because NULL = NULL never matches.

A pivot turns the values of one column into column names. In orders, status has three values: paid, refunded and pending. A pivot gives one column for each.

You write one aggregate for each new column. Chapter 3 showed SUM with CASE. The CASE picks the rows for one column, and ELSE 0 gives 0 to the other rows:

SELECT customer_id,
       ROUND(SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END), 2) AS paid,
       ROUND(SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END), 2) AS refunded,
       ROUND(SUM(CASE WHEN status = 'pending'  THEN amount ELSE 0 END), 2) AS pending
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Look at the cells for statuses a customer does not have. Customer 1 has no refunded order, so ELSE 0 gave 0. ROUND(…, 2) shows it as 0.00, like the other cells.

Now look at customer 3. The pending cell shows 0.00. But order 105 is pending, and its amount is unknown.

Order 104 gave a 0, and SUM skipped the NULL of order 105. So ELSE 0 hid the unknown amount.

The same idea with FILTER

Chapter 3 showed FILTER, a shorter form. It tells an aggregate to use only some rows. Here is what it gives when no rows pass the test:

SELECT customer_id,
       COUNT(*)      FILTER (WHERE status = 'paid')     AS paid_orders,
       SUM(amount)   FILTER (WHERE status = 'refunded') AS refunded_total
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

FILTER does not have an ELSE. Write COALESCE(SUM(amount) FILTER (...), 0) if you want a 0.

PostgreSQL, SQLite (3.30 and later), DuckDB and Databricks have FILTER. MySQL, SQL Server and BigQuery do not, so SUM with CASE is the choice that works everywhere.

Some databases have a PIVOT clause. It does the same job as SUM with CASE, with less typing. This table shows who has it:

The syntax is not the same in each database. This is the customer table from the last section, in SQL Server, which puts the new column names in square brackets:

-- SQL Server. This does not run in PostgreSQL.
SELECT customer_id, [paid], [refunded], [pending]
FROM (SELECT customer_id, status, amount FROM orders) AS source
PIVOT (SUM(amount) FOR status IN ([paid], [refunded], [pending])) AS p
ORDER BY customer_id;

Customer 3’s pending cell is NULL now. PIVOT adds only order 105, and its amount is unknown.

Wrap a column in COALESCE if you want 0 instead of NULL. That is one reason many people prefer SUM with CASE.

In SQL Server and Oracle, every source column that you do not pivot or add up becomes a group. Here, customer_id is the group.

Unpivoting is the opposite of pivoting. Several columns become one column of values, and the old column names become text.

This made-up table is wide. It has one column for each status, and one row for each customer:

The way that works in every database is one SELECT for each column, joined with UNION ALL. Each SELECT writes the column name as text:

SELECT customer_id, 'paid' AS status, paid AS orders
FROM status_counts
UNION ALL
SELECT customer_id, 'refunded', refunded
FROM status_counts
UNION ALL
SELECT customer_id, 'pending', pending
FROM status_counts
ORDER BY customer_id, status;

Most of these rows have orders = 0. To keep only real orders, put the stack in a CTE (chapter 4) and filter it with WHERE orders > 0. That gives 5 rows, as the next query shows.

In PostgreSQL, VALUES writes rows by hand, inside the query. LATERAL lets those rows use the columns of the current row. Together they do the job in one pass over the table:

SELECT s.customer_id, v.status, v.orders
FROM status_counts AS s
CROSS JOIN LATERAL (VALUES ('paid', s.paid),
                           ('refunded', s.refunded),
                           ('pending', s.pending)) AS v(status, orders)
WHERE v.orders > 0
ORDER BY s.customer_id, v.status;

SQL Server writes this as CROSS APPLY (VALUES …). SQL Server, Oracle, Snowflake, BigQuery and Databricks also have an UNPIVOT clause, which drops NULL cells by default. MySQL and SQLite usually use UNION ALL, which keeps them.

If one column holds an array, UNNEST turns each item into a row. PostgreSQL and BigQuery have UNNEST.

A report often needs detail rows and a total in one result. A subtotal is a total for a part of the rows. ROLLUP adds the subtotals and a grand total.

This query groups the orders by status, and adds one grand total row:

SELECT status,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (status)
ORDER BY status;

A NULL with two meanings

Now group by country and status. Dev has no country, so a NULL country can mean two things. GROUPING(column) gives 1 when the NULL is in a subtotal or total row, and 0 when it is a real value:

SELECT c.country,
       o.status,
       COUNT(*)           AS orders,
       SUM(o.amount)      AS revenue,
       GROUPING(c.country) AS g_country,
       GROUPING(o.status)  AS g_status
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY ROLLUP (c.country, o.status)
ORDER BY GROUPING(c.country), c.country, GROUPING(o.status), o.status;

Look at the three rows with NULL in country. The first is Dev’s real group: his country is missing, and g_country is 0.

The second is the subtotal for that group, and g_status is 1. The last is the grand total, and g_country is 1.

Without GROUPING(), the last two rows both show NULL in country and in status. A reader of the report cannot tell which one is the grand total.

GROUPING SETS and CUBE

ROLLUP makes groupings from left to right. GROUPING SETS lists the groupings you want. CUBE makes every combination.

This table shows the row counts on our six orders:

ROLLUP (a, b) is the same as GROUPING SETS ((a, b), (a), ()). With CUBE, n columns give 2 to the power n groupings, so it grows fast.

PostgreSQL, SQL Server, Oracle, Snowflake and BigQuery have all three. MySQL has only GROUP BY a, b WITH ROLLUP. SQLite has none of them.

Without ROLLUP

Without ROLLUP, stack the detail rows and the total with UNION ALL. A text label and an is_total column replace GROUPING():

SELECT status, COUNT(*) AS orders, SUM(amount) AS revenue, 0 AS is_total
FROM orders
GROUP BY status
UNION ALL
SELECT 'all statuses', COUNT(*), SUM(amount), 1
FROM orders
ORDER BY is_total, status;

The total row counts the orders table again. It does not add up the rows above it.

Reshaping queries rarely give an error. They give a result that looks fine and is wrong. Compare what you see with the usual cause:

Do three checks after every reshaping query:

1. Count the rows. Say the number you expect before you run the query.
2. Look at each empty cell. Is it 0, or is it NULL, and is that right?
3. For counts and sums, add up the detail rows. Does the sum match the total row?

Try check 3 on the two-column ROLLUP. The country subtotals are 60.00, 65.00, 15.50 and 80.00. They add up to 220.50, the grand total.

Each exercise opens in the SQL workspace with its own tables. They use the online and in-store orders of a shop, not the six orders in this chapter.

They follow the order of this chapter. The unpivot exercise uses a CTE from chapter 4.