CUBE Concept

CUBE: Every Possible Combination

CUBE is the most exhaustive member of the GROUPING SETS family (Topic 15.6). While ROLLUP only produces a hierarchical, right-to-left nested sequence of subtotals (Topic 15.7), CUBE(col1, col2, ...) produces every possible combination of the listed columns — a full power set of grouping levels.

CUBE(region, product) expands to GROUPING SETS ((region, product), (region), (product), ()) — all four possible combinations of "include region," "include product."

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

Example

sql
SELECT region, product, SUM(amount) AS total_amount
FROM orders
GROUP BY CUBE(region, product)
ORDER BY region, product;

Result (12 rows: 6 detail + 3 region subtotals + 2 product subtotals + 1 grand total):

Compared to ROLLUP(region, product)'s 10 rows, CUBE adds the two extra product-only subtotal rows (region=NULL, product='Gadget' = 530 and region=NULL, product='Widget' = 530) that ROLLUP never produces.

CUBE vs ROLLUP, Directly Compared

ROLLUP assumes a hierarchy (drop columns right-to-left only); CUBE makes no such assumption and computes every subset, so it grows combinatorially: with n grouping columns, CUBE produces 2^n grouping sets, versus ROLLUP's n + 1.

Why This Beats Manual Queries

Getting this same 12-row result manually would require four separate GROUP BY queries ((region, product), (region), (product), and none) combined with UNION ALL — four full scans of orders instead of one.

Dialect Support (Important Caveat)

Like GROUPING SETS, CUBE is supported in PostgreSQL, SQL Server, and Oracle. MySQL does not support `CUBE` at all (as of mainstream MySQL 8.x) — only the simpler WITH ROLLUP modifier exists there. On MySQL, replicating CUBE's output requires manually writing out all 2^n GROUP BY/UNION ALL combinations.

Edge Cases

• CUBE with 3+ columns grows fast: CUBE(a, b, c) produces 2^3 = 8 grouping sets — every subset from full detail down to the grand total.
• As with ROLLUP/GROUPING SETS, use GROUPING(column) to tell a real stored NULL apart from a subtotal-marker NULL.
• CUBE and ROLLUP can technically be combined or mixed with explicit GROUPING SETS entries in the same GROUP BY clause in dialects that support it (PostgreSQL, SQL Server), for custom combinations.

Key Takeaways / Interview Q&A

• Q: What does CUBE(region, product) compute that ROLLUP(region, product) does not?

A: The two product-only subtotal rows (Widget=530, Gadget=530 across all regions) — CUBE computes every combination, not just a hierarchical rollup.

• Q: How many grouping sets does CUBE(a, b, c) produce?

A: 2^3 = 8 (every subset of {a, b, c}, including the empty set).

• Q: How many rows does GROUP BY CUBE(region, product) return here?

A: 12 — 6 detail + 3 region subtotals + 2 product subtotals + 1 grand total.

• Q: Does MySQL support CUBE?

A: No — only PostgreSQL, SQL Server, and Oracle support CUBE; MySQL has neither CUBE nor full GROUPING SETS, only WITH ROLLUP.