SQL UNION ALL: Combine Results and Keep Duplicates

UNION ALL stacks the results of several queries, just like UNION, but it keeps every row, including duplicates. Because it does not have to look for duplicates, it is also faster.

UNION vs UNION ALL side by side#

Cities of employees and customers:

SELECT city FROM employees
UNION ALL
SELECT city FROM customers
ORDER BY city;

city
---------
Bengaluru
Chennai
Delhi
Delhi
Mumbai
Mumbai
Mumbai
Pune
Pune

All 9 rows (5 employees + 4 customers) are kept. With UNION, the same query returns 5 distinct cities.

When duplicates are correct#

Duplicates are often real data, not mistakes. In monthly_sales, the South region earned exactly 90 in both January and February. Those are two genuine sales figures that happen to be equal. Combine South's revenue rows with UNION and one of them disappears:

SELECT revenue FROM monthly_sales WHERE region = 'South'
UNION
SELECT revenue FROM monthly_sales WHERE region = 'South';

revenue
-------
90
125
130

Four months of data became three values. Whenever you will count or sum the result, use UNION ALL.

Combining tables with the same shape#

A common real use is merging tables that hold the same kind of data, such as yearly archives or regional copies:

SELECT month, 'North' AS region, revenue
FROM monthly_sales
WHERE region = 'North' AND month <= '2026-02'
UNION ALL
SELECT month, 'South', revenue
FROM monthly_sales
WHERE region = 'South' AND month <= '2026-02'
ORDER BY month, region;

month   | region | revenue
--------+--------+--------
2026-01 | North  | 120
2026-01 | South  | 90
2026-02 | North  | 150
2026-02 | South  | 90

In practice, each part would usually read from a different table (sales_2025, sales_2026, …).

Building a small summary#

UNION ALL can also stitch together several single-row queries into one report:

SELECT 'Employees' AS source, COUNT(*) AS n FROM employees
UNION ALL
SELECT 'Customers', COUNT(*) FROM customers
UNION ALL
SELECT 'Orders', COUNT(*) FROM orders;

source    | n
----------+--
Employees | 5
Customers | 4
Orders    | 4

Rules#

The same rules as UNION apply: the same number of columns, matched by position, with compatible types; names come from the first query; one ORDER BY at the end.

Common mistakes#

Practice#

SELECT 'Employees' AS source, COUNT(*) AS in_mumbai
FROM employees
WHERE city = 'Mumbai'
UNION ALL
SELECT 'Customers', COUNT(*)
FROM customers
WHERE city = 'Mumbai';

source    | in_mumbai
----------+----------
Employees | 2
Customers | 1

Key takeaway#