SQL Notes
Learn how to use UNION ALL in SQL to combine result sets from multiple queries while keeping all rows including duplicates, with performance comparisons to UNION.
UNION ALL is the faster sibling of UNION. While UNION takes the time to find and remove duplicate rows from the combined result, UNION ALL simply stacks all rows from both queries together — duplicates and all. No sorting, no comparison, no deduplication overhead.
In the real world, UNION ALL is actually used more often than UNION. Why? Because in many scenarios, you already know duplicates are impossible (the tables have different data), or you specifically want to keep all rows for accurate counting and reporting.
The performance difference is not trivial. For large datasets with millions of rows, the deduplication step in UNION requires sorting or hashing the entire combined result set, which consumes significant memory and CPU time. UNION ALL skips this entirely, making it dramatically faster. In data warehousing and ETL pipelines where you combine data from multiple sources, UNION ALL is almost always the correct choice because the source data is already known to be distinct.
UNION ALL vs UNION
Let us see the difference with concrete data:
OnlineOrders:
| OrderID | CustomerName | Amount |
|---|---|---|
| 1 | Rahul | 5000 |
| 2 | Priya | 3000 |
| 3 | Amit | 7000 |
StoreOrders:
| OrderID | CustomerName | Amount |
|---|---|---|
| 4 | Sneha | 2000 |
| 5 | Rahul | 5000 |
| 2 | Priya | 3000 |
-- UNION: Removes duplicate rows (Priya's 3000 order appears once)
SELECT CustomerName, Amount FROM OnlineOrders
UNION
SELECT CustomerName, Amount FROM StoreOrders;
-- Returns 5 rows-- UNION ALL: Keeps ALL rows including duplicates
SELECT CustomerName, Amount FROM OnlineOrders
UNION ALL
SELECT CustomerName, Amount FROM StoreOrders;
-- Returns 6 rows (Priya appears twice)UNION ALL Result:
| CustomerName | Amount |
|---|---|
| Rahul | 5000 |
| Priya | 3000 |
| Amit | 7000 |
| Sneha | 2000 |
| Rahul | 5000 |
| Priya | 3000 |
Both Rahul and Priya appear twice because UNION ALL preserves every row.
Why Use UNION ALL Over UNION?
Performance
UNION ALL is significantly faster because it skips the deduplication step:
| Operation | What happens internally |
|---|---|
| UNION | Combine rows + Sort + Remove duplicates |
| UNION ALL | Just combine rows (done!) |
For large datasets, this performance difference is dramatic. UNION on two tables with 1 million rows each requires sorting 2 million rows to find duplicates. UNION ALL just returns 2 million rows immediately.
Accuracy in Reporting
When you need accurate totals, UNION ALL is correct:
-- How much total revenue from all channels?
SELECT SUM(TotalRevenue) AS GrandTotal FROM (
SELECT SUM(Amount) AS TotalRevenue FROM OnlineOrders
UNION ALL
SELECT SUM(Amount) AS TotalRevenue FROM StoreOrders
UNION ALL
SELECT SUM(Amount) AS TotalRevenue FROM PhoneOrders
) AS AllRevenue;If you used UNION here and two channels had the same total, one would be eliminated — giving you an incorrect grand total.
Real-World Example: Activity Log
Combining events from different event tables where duplicates are impossible (different event types):
SELECT
UserID,
EventTime,
'Login' AS EventType,
NULL AS Details
FROM LoginEvents
UNION ALL
SELECT
UserID,
EventTime,
'Purchase',
CONCAT('Amount: ', Amount)
FROM PurchaseEvents
UNION ALL
SELECT
UserID,
EventTime,
'PageView',
PageURL
FROM PageViewEvents
ORDER BY EventTime DESC
LIMIT 100;Since a login can never be a duplicate of a purchase, UNION ALL is the correct and faster choice here.
Real-World Example: Quarterly Sales Report
-- Combine sales from all four quarters
SELECT 'Q1' AS Quarter, ProductID, SUM(Amount) AS Revenue
FROM Sales WHERE SaleDate BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY ProductID
UNION ALL
SELECT 'Q2', ProductID, SUM(Amount)
FROM Sales WHERE SaleDate BETWEEN '2026-04-01' AND '2026-06-30'
GROUP BY ProductID
UNION ALL
SELECT 'Q3', ProductID, SUM(Amount)
FROM Sales WHERE SaleDate BETWEEN '2026-07-01' AND '2026-09-30'
GROUP BY ProductID
UNION ALL
SELECT 'Q4', ProductID, SUM(Amount)
FROM Sales WHERE SaleDate BETWEEN '2026-10-01' AND '2026-12-31'
GROUP BY ProductID
ORDER BY Quarter, Revenue DESC;Each quarter's data is independent, so UNION ALL is both correct and performant.
Real-World Example: Multi-Table Search
-- Search for "laptop" across all product categories
SELECT ProductID, Name, Price, 'Electronics' AS Category
FROM Electronics WHERE Name LIKE '%laptop%'
UNION ALL
SELECT ProductID, Name, Price, 'Computers'
FROM Computers WHERE Name LIKE '%laptop%'
UNION ALL
SELECT ProductID, Name, Price, 'Refurbished'
FROM Refurbished WHERE Name LIKE '%laptop%'
ORDER BY Price ASC;UNION ALL with Aggregate Queries
A common pattern is using UNION ALL inside a subquery for reporting:
-- Department-wise total employees from multiple office locations
SELECT Department, SUM(EmpCount) AS TotalEmployees
FROM (
SELECT Department, COUNT(*) AS EmpCount FROM MumbaiOffice GROUP BY Department
UNION ALL
SELECT Department, COUNT(*) FROM DelhiOffice GROUP BY Department
UNION ALL
SELECT Department, COUNT(*) FROM BangaloreOffice GROUP BY Department
) AS AllOffices
GROUP BY Department
ORDER BY TotalEmployees DESC;When to Use UNION vs UNION ALL
| Scenario | Use |
|---|---|
| Combining data from different sources with possible overlaps | UNION |
| Combining data where duplicates are impossible | UNION ALL |
| Performance is critical and duplicates are acceptable | UNION ALL |
| Building reports that need accurate row counts | UNION ALL |
| Creating a unique list from multiple sources | UNION |
| Combining different event types into a timeline | UNION ALL |
Multiple UNION ALL Operations
You can chain as many UNION ALL operations as needed:
SELECT * FROM January_Sales
UNION ALL
SELECT * FROM February_Sales
UNION ALL
SELECT * FROM March_Sales
UNION ALL
SELECT * FROM April_Sales
UNION ALL
SELECT * FROM May_Sales
UNION ALL
SELECT * FROM June_Sales;The database processes these efficiently, simply concatenating the results.
Common Mistakes to Avoid
Mistake 1: Using UNION when you need UNION ALL for correct totals. If two departments both have 50000 as their total salary, UNION removes one. UNION ALL keeps both.
Mistake 2: Using UNION ALL when you need unique results. If duplicates would confuse users (like a contact list), use UNION to deduplicate.
Mistake 3: Forgetting ORDER BY applies to the final result. Just like UNION, ORDER BY goes only at the very end of all UNION ALL operations.
Mistake 4: Not adding a source identifier. Without a column indicating which table each row came from, debugging becomes difficult.
Best Practices
- Default to UNION ALL unless you specifically need deduplication
- Add a source column to identify where each row originated
- Use inside subqueries for combining data before aggregation
- Match column types exactly to avoid implicit conversions
- Place ORDER BY and LIMIT at the end for the final combined result
- Consider UNION ALL for ETL pipelines where speed matters most
UNION ALL is the workhorse of SQL set operators. It is faster, simpler, and often more correct than UNION. Whenever you combine data from multiple sources and duplicates are either impossible or desired, UNION ALL should be your default choice.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for UNION ALL Operator in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, set, operators, union
Related SQL Complete Guide Topics