What is a UNION ALL in SQL?

UNION ALL is SQL’s way of combining result sets from multiple queries without worrying about duplicates. If you’ve used UNION before, UNION ALL is its faster, less fussy sibling that keeps every single row from all your queries, even if some rows are identical.

So UNION removes duplicate rows automatically, while UNION ALL keeps everything.

The Basic Syntax

The syntax is simple. You just place UNION ALL in between the two SELECT statements:

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

Like with UNION, both SELECT statements must have a matching number of columns, and the data types for the columns must be compatible. But unlike UNION, you get back every row from both queries, duplicates included.

Example

Let’s look at a scenario where you’re tracking software deployments across development and staging environments. You might have the following tables:

-- Create sample tables
CREATE TABLE DevDeployments (
    DeploymentID INT,
    AppName VARCHAR(100),
    DeployedBy VARCHAR(50)
);

CREATE TABLE StagingDeployments (
    DeploymentID INT,
    AppName VARCHAR(100),
    DeployedBy VARCHAR(50)
);

-- Insert sample data
INSERT INTO DevDeployments VALUES 
(1, 'PaymentAPI', 'Sarah'),
(2, 'UserService', 'Mike'),
(3, 'NotificationHub', 'Sarah');

INSERT INTO StagingDeployments VALUES 
(101, 'PaymentAPI', 'Sarah'),
(102, 'InventoryAPI', 'Chen'),
(103, 'NotificationHub', 'Sarah');

Now you could use UNION ALL to combine data from both tables:

SELECT AppName, DeployedBy FROM DevDeployments
UNION ALL
SELECT AppName, DeployedBy FROM StagingDeployments;

Result:

AppName          DeployedBy
---------------  ----------
PaymentAPI       Sarah     
UserService      Mike      
NotificationHub  Sarah     
PaymentAPI       Sarah     
InventoryAPI     Chen      
NotificationHub  Sarah     

This returns six rows total. Notice that both “PaymentAPI” deployed by Sarah and “NotificationHub” deployed by Sarah appear twice in the results because they exist in both tables. With UNION ALL, you see every deployment regardless of whether it’s a duplicate.

Why Use UNION ALL Over UNION?

There are two main reasons you might choose UNION ALL over UNION. These basically come down to data integrity and performance:

• For data integrity, sometimes duplicates are meaningful. If you’re creating an audit log or tracking individual transactions, every row matters. You don’t want the database deciding which duplicate to keep and which to discard.
• Performance-wise, UNION ALL is significantly faster because it doesn’t need to check for duplicates. When you use regular UNION, the database has to sort the combined results and compare rows to find and eliminate duplicates. That’s extra processing time and memory usage. With UNION ALL, the database just stacks the results together and returns them immediately. That said, you would need to want duplicates for this to be an appropriate option. If you actually want to eliminate duplicates, then maybe you just need to accept the performance hit from using UNION.

Combining More Than Two Queries

UNION ALL isn’t limited to just two SELECT statements. You can chain as many as you need:

CREATE TABLE NorthRegion (RegionID INT, Revenue DECIMAL(10,2));
CREATE TABLE SouthRegion (RegionID INT, Revenue DECIMAL(10,2));
CREATE TABLE EastRegion (RegionID INT, Revenue DECIMAL(10,2));
CREATE TABLE WestRegion (RegionID INT, Revenue DECIMAL(10,2));

INSERT INTO NorthRegion VALUES (1, 45000.00);
INSERT INTO SouthRegion VALUES (2, 52000.00);
INSERT INTO EastRegion VALUES (3, 48000.00);
INSERT INTO WestRegion VALUES (4, 51000.00);

SELECT RegionID, Revenue, 'North' AS Region FROM NorthRegion
UNION ALL
SELECT RegionID, Revenue, 'South' FROM SouthRegion
UNION ALL
SELECT RegionID, Revenue, 'East' FROM EastRegion
UNION ALL
SELECT RegionID, Revenue, 'West' FROM WestRegion
ORDER BY Revenue DESC;

Result:

RegionID  Revenue  Region
--------  -------  ------
2         52000    South 
4         51000    West  
3         48000    East  
1         45000    North 

This pattern is common when you’re aggregating data from partitioned tables or multiple similar sources. Adding the literal ‘Region’ column helps you track where each row came from in the combined result.

Performance at Scale

The performance difference between UNION and UNION ALL becomes dramatic with large datasets. If you’re combining tables with millions of rows, UNION ALL can be orders of magnitude faster. The duplicate elimination process in UNION requires sorting and comparing every row, which scales poorly.

That said, don’t use UNION ALL blindly just for speed. If your data actually has duplicates and you need unique results, you’ll end up with incorrect data. It’s about choosing the right tool for the job.

Filtering and Sorting

You can add WHERE clauses to individual SELECT statements to filter before combining:

SELECT AppName, DeployedBy FROM DevDeployments
WHERE DeployedBy = 'Sarah'
UNION ALL
SELECT AppName, DeployedBy FROM StagingDeployments
WHERE DeployedBy = 'Sarah';

Result:

AppName          DeployedBy
---------------  ----------
PaymentAPI       Sarah     
NotificationHub  Sarah     
PaymentAPI       Sarah     
NotificationHub  Sarah     

This gives you all of Sarah’s deployments from both environments. Filtering before the UNION ALL operation is more efficient than filtering the combined result.

For sorting, ORDER BY goes at the end and applies to the entire result set:

SELECT AppName, DeployedBy FROM DevDeployments
UNION ALL
SELECT AppName, DeployedBy FROM StagingDeployments
ORDER BY AppName, DeployedBy;

Result:

AppName          DeployedBy
---------------  ----------
InventoryAPI     Chen      
NotificationHub  Sarah     
NotificationHub  Sarah     
PaymentAPI       Sarah     
PaymentAPI       Sarah     
UserService      Mike     

The combined results get sorted alphabetically by AppName first, then by DeployedBy within each app.

Summary

UNION ALL takes all rows from multiple queries and combines them without removing duplicates. Use it when you need complete data with every row preserved, when you know duplicates don’t exist or shouldn’t be removed, or when you need better performance and duplicates aren’t a concern.

Most importantly, use UNION ALL when the semantics of your data demand it. If each row represents a distinct fact or event, don’t let the database arbitrarily throw some away just because they happen to look the same in the columns you’re selecting.