Discover Top 5 Union vs Union All Differences

Union vs Union All – Combining data from multiple result sets is a common requirement when working with SQL Server. UNION and UNION ALL are powerful operators designed for this purpose—but knowing when and how to use each one effectively can impact query performance and accuracy. This article explores every aspect of UNION vs UNION ALL in SQL Server for developers, DBAs, and data enthusiasts.

🏢 Introduction – UNION vs UNION ALL

In SQL Server, UNION vs UNION ALL combines the result sets of two or more SELECT statements. While they may seem similar, they behave differently, especially regarding duplicate handling and performance. This article provides a detailed comparison of these two operators, complete with examples, best practices, and FAQs.

🕰️ A Glimpse into History

Combining result sets using UNION dates back to early versions of SQL standards in the 1980s. Microsoft SQL Server has supported both UNION and UNION ALL since its early iterations, enhancing the ability to aggregate data efficiently across multiple tables or queries.

Advantages of UNION in SQL Server

A few advantages of UNION are given below for better understanding:-

✅ It eliminates duplicate rows automatically from the result.

The UNION command in SQL Server can remove duplicate rows from the final result set, ensuring unique output data. Such output type is beneficial when merging datasets where redundancy could cause reporting errors.

✅ It ensures Data Integrity.

By eliminating duplicates, UNION helps maintain data integrity in scenarios like generating consolidated reports or dashboards from multiple tables or views.

✅ It helps to simplify the Result Set.

The cleaned result is easier to read and interpret, making it ideal for human-readable outputs or downstream applications that expect unique rows.

✅ It Helps in Audit or Validation

Having only distinct rows in data validation or audit processes simplifies identifying anomalies or inconsistencies.

✅ It Supports Complex Querying Needs

UNION is useful when combining data from multiple sources and avoiding duplication without writing additional filtering logic.

Disadvantages of UNION in SQL Server

A few disadvantages of UNION are given below for better understanding:-

❌ Performance Overhead

UNION has to sort and compare all rows to remove duplicates, which can slow down performance—especially for large datasets.

❌ It Increases Resource Usage

The duplicate elimination process uses more CPU and memory than UNION ALL, potentially impacting server performance.

❌ It May Mask Duplicate Issues

UNION removes duplicates silently, which could hide data issues that need attention in systems where duplicates are unexpected.

❌ It is not suitable for Performance-Critical Scenarios.

In real-time or high-performance systems, the cost of duplicate removal may outweigh the benefit, making UNION a less ideal choice.

❌ Implicit Sorting Can Affect Execution Plans

The internal sorting required for UNION can lead to more complex execution plans and longer query optimization times.

Different Parameters of UNION vs UNION ALL

The below table will give you a clear picture of UNION vs UNION ALL.

Parameter	UNION	UNION ALL
Performance	A bit Slower	Faster Than Union
Use Case	It works on merged data	Raw data consolidation
Duplicate Elimination	Yes, Removes Duplicate Data	No, It does not Remove Duplicate Data
Sorting	Implied for duplicate check	Sorting of Data is not required
Memory Usage	It consumes more memory.	It consumes less memory.

Different Parameters of UNION vs UNION ALL

🔀 UNION vs UNION ALL – Background Steps

⚙️ Internal Steps – UNION

SQL Server performs the following behind the scenes:

Execute both SELECT queries and fetch the result sets.

Combine the results vertically (i.e., append rows).

Sort or hash the combined results to identify duplicates.

Remove duplicates.

Return the distinct final result to the user.

Execution Plan (Behind-the-Scenes):

Logical operator: Concat (to combine).

Physical operators: Sort Distinct or Hash Aggregate.

These steps add CPU, I/O, and TEMPDB usage.

⚙️ Internal Steps – UNION ALL

Steps:

Execute both SELECT queries.

Append results from the second query to the first.

Return combined output directly — no sorting, no duplicate removal.

Execution Plan (Behind-the-Scenes):

Just a Concat operator (streaming concatenation).

No Sort or Hash Match.

Hence, faster execution and lower resource use.

🧠 Key Optimization Insights on UNION vs UNION ALL:

Use UNION ALL whenever you don’t need to eliminate duplicates.

UNION should be used only when data uniqueness is required.

🧾 Syntax of UNION vs UNION ALL in SQL Server

-- Syntax of UNION

SELECT <Column Name 1>, <Column Name 2>, <Column Name 3>, .....<Column Name N>
FROM <Table Name 1>
WHERE <Condition 1>   --Put condition as per your requirement

UNION

SELECT <Column Name 1>, <Column Name 2>, <Column Name 3>, .....<Column Name N>
FROM <Table Name 2>
WHERE <Condition 1>   --Put condition as per your requirement


-- Syntax of UNION ALL

SELECT <Column Name 1>, <Column Name 2>, <Column Name 3>, .....<Column Name N>
FROM <Table Name 1>
WHERE <Condition 1>   --Put condition as per your requirement

UNION ALL

SELECT <Column Name 1>, <Column Name 2>, <Column Name 3>, .....<Column Name N>
FROM <Table Name 2>
WHERE <Condition 1>   --Put condition as per your requirement

🔐 Which Permission is Needed to Run UNION vs UNION ALL?

To use UNION or UNION ALL, the user must have SELECT permission on all the involved tables or views. No additional or special permissions are required.

A Few Examples of UNION vs UNION ALL with Queries

📌 Example – 1: To fetch unique vendor names from the transitions tables

SELECT v.VendorName FROM Vendors v
INNER JOIN Purchases p ON p.VendorID = v.VendorID
UNION
SELECT v.VendorName FROM Vendors v
INNER JOIN Sales s ON s.VendorID = v.VendorID

The execution plan with the UNION clause of the above query clearly shows that the Sort operation is taking 47% of the total execution time.

📌 Example – 2: To fetch all vendor names, including duplicates from the transitions tables (A good example of Joins)

SELECT v.VendorName FROM Vendors v
INNER JOIN Purchases p ON p.VendorID = v.VendorID
UNION ALL
SELECT v.VendorName FROM Vendors v
INNER JOIN Sales s ON s.VendorID = v.VendorID

The execution plan with the UNION ALL clause of the above query clearly shows that no Sort operation is there. Hence, in this case, after using the UNION ALL, we can save 47% execution time.

📌 Example – 3: City-wise Total Fee Collection for all schools & colleges (A good example of UNION with Subqueries)

-- Script to create SchoolWiseFeeCollection table

CREATE TABLE SchoolWiseFeeCollection (
    SchoolWiseFeeCollectionID INT IDENTITY(1,1) PRIMARY KEY,
    SchoolName NVARCHAR(100),
    City NVARCHAR(50),
    TotalNoOfStudents INT,
    FeePerStudent DECIMAL(10,2),
    TotalCollection AS (TotalNoOfStudents * FeePerStudent) PERSISTED,
    CollectionDate DATE
);

--Insert statement to insert sample data in SchoolWiseFeeCollection table

INSERT INTO SchoolWiseFeeCollection (SchoolName, City, TotalNoOfStudents, FeePerStudent, CollectionDate)
VALUES 
('Delhi Public School', 'Delhi', 950, 1800.00, '2025-04-01'),
('St. Xavier''s High School', 'Mumbai', 850, 2000.00, '2025-04-02'),
('National Public School', 'Bengaluru', 700, 2200.00, '2025-04-03'),
('Modern School', 'Delhi', 920, 2100.00, '2025-04-01'),
('Loyola School', 'Chennai', 750, 1950.00, '2025-04-04'),
('Don Bosco High School', 'Kolkata', 600, 2050.00, '2025-04-01'),
('Springdales School', 'Jaipur', 500, 1900.00, '2025-04-05'),
('DAV Public School', 'Patna', 800, 1800.00, '2025-04-06'),
('Ryan International', 'Hyderabad', 650, 2000.00, '2025-04-07'),
('Mount Carmel School', 'Lucknow', 780, 1700.00, '2025-04-08'),
('Sunbeam School', 'Varanasi', 720, 1600.00, '2025-04-09'),
('City Montessori School', 'Lucknow', 1000, 1850.00, '2025-04-10'),
('La Martiniere', 'Kolkata', 550, 2100.00, '2025-04-11'),
('The Millennium School', 'Noida', 770, 2300.00, '2025-04-12'),
('Amity International School', 'Gurgaon', 660, 2200.00, '2025-04-13'),
('Greenwood High', 'Bengaluru', 900, 2500.00, '2025-04-14'),
('Oakridge International', 'Hyderabad', 620, 2700.00, '2025-04-15'),
('Sanskriti School', 'Delhi', 830, 2600.00, '2025-04-16'),
('Heritage School', 'Gurgaon', 640, 2400.00, '2025-04-17'),
('The Shri Ram School', 'Delhi', 790, 2550.00, '2025-04-18'),
('Kendriya Vidyalaya', 'Bhopal', 1100, 1000.00, '2025-04-01'),
('Army Public School', 'Pune', 880, 1850.00, '2025-04-02'),
('Jawahar Navodaya Vidyalaya', 'Ranchi', 720, 1200.00, '2025-04-03'),
('Birla High School', 'Kolkata', 610, 2100.00, '2025-04-04'),
('St. John’s School', 'Bhopal', 500, 1750.00, '2025-04-05'),
('St. Thomas School', 'Dehradun', 550, 1900.00, '2025-04-06'),
('International Public School', 'Indore', 670, 2000.00, '2025-04-07'),
('Bright Future Academy', 'Kanpur', 700, 1800.00, '2025-04-08'),
('Scholars Home', 'Dehradun', 480, 1950.00, '2025-04-09'),
('Little Flower School', 'Guwahati', 590, 1600.00, '2025-04-10'),
('Sacred Heart Convent', 'Jamshedpur', 630, 2000.00, '2025-04-11'),
('St. Paul''s School', 'Darjeeling', 400, 2200.00, '2025-04-12'),
('Mother''s International', 'Delhi', 810, 2500.00, '2025-04-13'),
('Sardar Patel Vidyalaya', 'Delhi', 750, 2400.00, '2025-04-14'),
('Jain International Residential School', 'Bangalore', 520, 2700.00, '2025-04-15'),
('Nalanda Public School', 'Mumbai', 680, 2100.00, '2025-04-16'),
('Bhavan''s Vidya Mandir', 'Kochi', 790, 2000.00, '2025-04-17'),
('St. Mary''s School', 'Nagpur', 670, 1900.00, '2025-04-18'),
('SBOA School', 'Chennai', 720, 1950.00, '2025-04-19'),
('Ramakrishna Mission School', 'Kolkata', 550, 1700.00, '2025-04-20'),
('Air Force School', 'Delhi', 810, 1800.00, '2025-04-21'),
('Kendriya Vidyalaya', 'Ahmedabad', 1000, 1600.00, '2025-04-22'),
('Delhi Public School', 'Raipur', 860, 1850.00, '2025-04-23'),
('DAV School', 'Jalandhar', 590, 1750.00, '2025-04-24'),
('St. Anne’s School', 'Bhubaneswar', 610, 1900.00, '2025-04-25'),
('St. Joseph’s Convent', 'Bargarh', 520, 1850.00, '2025-04-26'),
('Oxford Public School', 'Bokaro', 540, 1700.00, '2025-04-27'),
('Vivekananda School', 'Rourkela', 600, 1650.00, '2025-04-28'),
('St. Xavier’s', 'Amritsar', 580, 1800.00, '2025-04-29'),
('Green Valley School', 'Shillong', 400, 1600.00, '2025-04-30');

-- Script to create CollegeWiseFeeCollection table

CREATE TABLE CollegeWiseFeeCollection (
    CollegeWiseFeeCollectionID INT IDENTITY(1,1) PRIMARY KEY,
    CollegeName NVARCHAR(100),
    City NVARCHAR(50),
    TotalNoOfStudents INT,
    FeePerStudent DECIMAL(10,2),
    TotalCollection AS (TotalNoOfStudents * FeePerStudent) PERSISTED,
    CollectionDate DATE
);

--Insert statement to insert sample data in CollegeWiseFeeCollection table

INSERT INTO CollegeWiseFeeCollection (CollegeName, City, TotalNoOfStudents, FeePerStudent, CollectionDate)
VALUES
('St. Stephen''s College', 'Delhi', 1500, 35000.00, '2025-04-01'),
('Loyola College', 'Chennai', 1300, 32000.00, '2025-04-02'),
('Presidency College', 'Kolkata', 1200, 30000.00, '2025-04-03'),
('St. Xavier''s College', 'Mumbai', 1100, 34000.00, '2025-04-04'),
('Fergusson College', 'Pune', 1000, 33000.00, '2025-04-05'),
('Miranda House', 'Delhi', 1400, 35500.00, '2025-04-06'),
('Christ University', 'Bengaluru', 1700, 45000.00, '2025-04-07'),
('Hansraj College', 'Delhi', 1250, 32500.00, '2025-04-08'),
('Symbiosis College', 'Pune', 1150, 42000.00, '2025-04-09'),
('Lady Shri Ram College', 'Delhi', 1300, 36500.00, '2025-04-10'),
('Hindu College', 'Delhi', 1350, 34000.00, '2025-04-11'),
('Ramjas College', 'Delhi', 1100, 31000.00, '2025-04-12'),
('Banaras Hindu University', 'Varanasi', 1800, 27000.00, '2025-04-13'),
('Jadavpur University', 'Kolkata', 1600, 29000.00, '2025-04-14'),
('Delhi College of Arts & Commerce', 'Delhi', 1050, 31000.00, '2025-04-15'),
('Stella Maris College', 'Chennai', 950, 28000.00, '2025-04-16'),
('Mount Carmel College', 'Bengaluru', 1250, 30000.00, '2025-04-17'),
('Maharaja Sayajirao University', 'Vadodara', 1150, 25000.00, '2025-04-18'),
('Aligarh Muslim University', 'Aligarh', 1400, 26000.00, '2025-04-19'),
('Osmania University', 'Hyderabad', 1600, 28500.00, '2025-04-20'),
('Nizam College', 'Hyderabad', 950, 27000.00, '2025-04-21'),
('Panjab University', 'Chandigarh', 1450, 32000.00, '2025-04-22'),
('Sri Venkateswara College', 'Delhi', 1100, 33500.00, '2025-04-23'),
('NM College', 'Mumbai', 1300, 34000.00, '2025-04-24'),
('KC College', 'Mumbai', 1250, 33000.00, '2025-04-25'),
('KJ Somaiya College', 'Mumbai', 1400, 35000.00, '2025-04-26'),
('MOP Vaishnav College', 'Chennai', 1000, 28000.00, '2025-04-27'),
('Ramakrishna Mission Vidyamandira', 'Belur', 850, 26000.00, '2025-04-28'),
('Patna Women’s College', 'Patna', 950, 24000.00, '2025-04-29'),
('BIT Mesra', 'Ranchi', 1000, 46000.00, '2025-04-30'),
('NIT Rourkela', 'Rourkela', 950, 47000.00, '2025-04-01'),
('KIIT University', 'Bhubaneswar', 1200, 48000.00, '2025-04-02'),
('VIT Vellore', 'Vellore', 1500, 50000.00, '2025-04-03'),
('Manipal University', 'Manipal', 1300, 51000.00, '2025-04-04'),
('SRM University', 'Chennai', 1400, 49000.00, '2025-04-05'),
('Jamia Millia Islamia', 'Delhi', 1600, 27000.00, '2025-04-06'),
('Gujarat University', 'Ahmedabad', 1250, 31000.00, '2025-04-07'),
('Lucknow University', 'Lucknow', 1200, 28000.00, '2025-04-08'),
('University of Allahabad', 'Prayagraj', 1100, 26000.00, '2025-04-09'),
('Ravenshaw University', 'Cuttack', 1050, 25000.00, '2025-04-10'),
('Assam University', 'Silchar', 900, 24000.00, '2025-04-11'),
('Gauhati University', 'Guwahati', 950, 25500.00, '2025-04-12'),
('Rajiv Gandhi University', 'Itanagar', 850, 23000.00, '2025-04-13'),
('IGNOU', 'Delhi', 2000, 10000.00, '2025-04-14'),
('Central University of Punjab', 'Bathinda', 780, 26000.00, '2025-04-15'),
('North Eastern Hill University', 'Shillong', 820, 24500.00, '2025-04-16'),
('Central University of Kerala', 'Kasaragod', 750, 23500.00, '2025-04-17'),
('TISS', 'Mumbai', 900, 39000.00, '2025-04-18'),
('Amity University', 'Noida', 1600, 47000.00, '2025-04-19'),
('Shiv Nadar University', 'Greater Noida', 850, 46000.00, '2025-04-20');

--Query to fetch the City wise Total Fee Collection

SELECT * FROM (SELECT City, 'School' AS Mode,SUM(TotalCollection) AS TotalSales
FROM SchoolWiseFeeCollection
GROUP BY City
UNION
SELECT City, 'College' AS Mode, SUM(TotalCollection) AS TotalSales
FROM CollegeWiseFeeCollection
GROUP BY City) aa 
Order By City;

📌 Example – 4: UNION ALL with Date Filters, Derived Columns and Sub-Queries

-- Use of UNION ALL with Date Filters, Derived Columns and Sub-Queries

SELECT * FROM 
(SELECT SchoolWiseFeeCollectionID FeeCollectionID, CollectionDate, 'School' AS OrderType
FROM SchoolWiseFeeCollection
WHERE CollectionDate >= '2025-04-01'
UNION ALL
SELECT CollegeWiseFeeCollectionID FeeCollectionID, CollectionDate, 'College' AS OrderType
FROM CollegeWiseFeeCollection
WHERE CollectionDate >= '2025-04-01') aa
Order By FeeCollectionID

Example - 4 : UNION ALL with Date Filters and Sub-Queries

📌 Example – 5: Using UNION with ORDER BY (Applied to the Final Query Only with ASC or DESC)

-- Use of UNION clause with ORDER BY (Applied to the Final Query result. We can use ASC or DESC to set the order of a column's value)

SELECT * FROM 
(SELECT City, 'School' AS Mode,SUM(TotalCollection) AS TotalSales
FROM SchoolWiseFeeCollection
GROUP BY City
UNION
SELECT City, 'College' AS Mode, SUM(TotalCollection) AS TotalSales
FROM CollegeWiseFeeCollection
GROUP BY City) aa Order By City, TotalSales DESC;

Best Practices of UNION vs UNION ALL

✅ Always ensure column compatibility between queries.

✅ Use UNION ALL when performance matters and duplicates aren’t a concern.

✅ Avoid unnecessary UNION if duplicates are impossible.

✅ Test performance impact with execution plans.

✅ Always Filter and try to limit the data before using the UNION or UNION ALL command in your query.

Conclusion

A better understanding of UNION vs. UNION ALL in SQL Server can significantly improve a developer’s query writing skills and system performance. Selecting the right operator is key whether you’re merging large datasets or simply deduplicating rows. Follow the best practices and examples provided to make informed decisions in your SQL journey.

Q: What is the main difference between UNION vs UNION ALL?
Ans: UNION removes duplicates; UNION ALL includes them.

Q: Which one performs faster—UNION vs UNION ALL?
Ans: UNION ALL is faster due to the lack of duplicate checks.

Q: Can I use ORDER BY with UNION vs UNION ALL?
Ans: Yes, but only after the final SELECT statement.

Q: Do both require the same number of columns in SELECT?
Ans: Yes, column counts and data types must match.

Q: Can I use UNION for more than two SELECTs?
Ans: Yes, multiple SELECT statements can be combined.

Q: When should I avoid using UNION?
Ans: Avoid UNION when performance is critical and duplicates are not an issue.

Q: Does UNION ALL impact indexing?
Not directly, but underlying query performance can be influenced.

Q: Is data type conversion allowed in UNION?
Ans: Implicit conversion may occur, but matching types is best.

Q: How does SQL Server handle NULLs in UNION?
Ans: NULLs are considered values; duplicates with NULLs are removed in UNION.

Q: Can I combine views using UNION?
Ans: As long as the SELECT structure is compatible.