Level 1 · One activity table with UNION ALL

A join puts two tables side by side: more columns. UNION ALL puts two query results on top of each other: more rows. You reach for it whenever the same kind of number lives in different tables and you want it in one result.

How UNION ALL works

SELECT 'orders' AS metric, CAST(DATE_TRUNC('month', order_date) AS DATE) AS month, COUNT(*) AS value
FROM orders
WHERE status = 'completed' AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
GROUP BY month
UNION ALL
SELECT 'returns', CAST(DATE_TRUNC('month', returned_on) AS DATE), COUNT(*)
FROM returns
WHERE returned_on >= '2025-01-01' AND returned_on < '2026-01-01'
GROUP BY 2
ORDER BY month, metric;

Each SELECT is a complete query with its own FROM, WHERE and GROUP BY. The database runs them separately and stacks the results. Three rules apply:

• every SELECT returns the same number of columns,
• columns are matched by position, not by name, so the types in each position must be compatible,
• the result’s column names come from the first SELECT. That is why only the first one needs aliases.

ORDER BY is written once, at the very end, and sorts the combined rows.

The label column

Without 'orders' AS metric, you would get a column of months and counts with no way to tell which number is which. A constant text column is the standard trick: it travels with every row of its SELECT and becomes the series name in a chart.

On the Harbor & Pine data, January 2025 comes out as 268 completed orders, 64 returns and 960 sessions. November jumps to 651 orders and 2,609 sessions: the holiday season is visible in all three metrics at once, which is exactly why Kevin wants them in one table.

This “long” layout (metric, period, value) is what most charting tools and BI dashboards expect. If someone later wants one column per metric, conditional aggregation from chapter 6 pivots it back.

Common mistakes

• ORDER BY inside a branch. ... GROUP BY month ORDER BY month UNION ALL SELECT ... is a syntax error. Sort once, after the last SELECT.
• Columns in a different order. If the second SELECT returns the count before the month, the database matches by position and tries to put a number in the date column. Some engines raise an error, others quietly cast, and you get nonsense either way.
• Different date grains. Grouping orders by month and sessions by day stacks fine but makes a chart that means nothing. Keep the grain identical in every branch.
• Different filters. If one branch covers 2025 and another forgot its date filter, the stack still runs. Read every WHERE clause side by side.

In SQL Server

UNION ALL works the same way. Month starts are usually written DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) (or DATETRUNC(month, order_date) from SQL Server 2022), and you group by the expression rather than by position, because T-SQL does not accept GROUP BY 2.