UNION vs UNION ALL — The Art of Combining Without Confusion
When we are working with databases, we often need to report related data from multiple tables. Instead of running separate queries and manually combining results, SQL allows us to merge query results efficiently using operators like UNION and UNION ALL.
Both operators help us combine the results of two or more SELECT statements, but they differ in how they handle duplicates, performance, and use cases. Understanding the differences between UNION and UNION ALL is important for writing optimized and reliable SQL queries.
WHAT IS UNION?
The UNION operator in SQL is used to combine the results of two or more SELECT statements into a single output. By default, UNION removes duplicate records between the combined queries. This ensures that each row in the final result is unique.
Syntax for UNION:
In this syntax, SQL Server executes both SELECT statements and merges the results into one output, eliminating any duplicate rows.
WHAT IS UNION ALL?
The UNION ALL operator in SQL is used to combine the results of two or more SELECT statements into a single output but does not remove duplicate records. It simply combines all rows from the SELECT statements, including duplicates.
Syntax Example:
Here, the result will include all rows from both tables even if some rows appear in both.
Because no duplicate-checking process is performed in UNION ALL.
DIFFERENCE BETWEEN UNION AND UNION ALL
| Feature | UNION | UNION ALL |
| Duplicate Handling | Removes duplicate rows | Includes duplicate rows |
| Performance | Slower due to duplicate elimination process | Faster because no duplicate check is done |
| Use Case | When unique records are required | When all records are needed |
| Sorting | It does not guarantee any specific order | It maintains the order of combined queries |
| Memory Usage | Higher (due to sorting for distinct) | Lower |
At SQL School.
We have been providing Trainings, Projects and Placements for last 20 years exclusively on SQL and Data Stack Technologies.
- 100% Practical Training
- Real-Time Projects
- 1:1 Mentorship
- Daily Assignments & Weekly Feedback
- Resume & Interview Support
- Certification Guidance
WHEN TO USE UNION VS UNION ALL IN REAL-WORLD SCENARIOS
When To Use UNION:
- When we need to remove duplicate records and show only unique results.
- When we want to maintain data accuracy and integrity across merged data.
- When don’t require output data to be in specific order.
When To Use UNION:
- Performance is important and duplicates don’t affect your output.
- You’re combining large or time-based datasets like logs or transactions.
- You’re performing ETL or data warehouse operations that need all records.
KEY REQUIREMENTS FOR USING UNION:
- Same Number of Columns:
Each SELECT statement within the UNION must return the same number of columns.
- Similar Data Types:
Corresponding columns in each SELECT statement must have compatible data types.
- Same Order of Columns:
The order of the columns in each SELECT statement must be consistent, as the UNION operator combines based on column position
LET’S UNDERSTAND UNION AND UNION ALL WITH A REAL-WORLD EXAMPLE
Create an Employee_table with EMP_ID, EMP_NAME, EMP_COUNTRY, EMPSAL columns and insert the values.
REQUIREMENT: HOW TO REPORT LIST OF EMPLOYEES FROM USA AND CANADA USING UNION OPERATOR
SELECT * FROM Employee_table WHERE EMP_COUNTRY = ‘CANADA’
UNION
SELECT * FROM Employee_table WHERE EMP_COUNTRY = ‘USA’
OUTPUT: We get UNIQUE values in output. Using UNION, the combined result merges data and removes duplicates but the order can be unpredictable (e.g., Canada rows might come before USA rows or vice versa).
REQUIREMENT: HOW TO REPORT LIST OF EMPLOYEES FROM USA AND CANADA USING UNION ALL OPERATOR
SELECT * FROM Employee_table WHERE EMP_COUNTRY = ‘CANADA’
UNION ALL
SELECT * FROM Employee_table WHERE EMP_COUNTRY = ‘USA’
OUTPUT: We get all the values of CANADA and USA including the duplicate values. Using UNION ALL, the order is preserved exactly as the queries are combined, e.g., all Canada employees first, followed by all USA employees.
UNION and UNION ALL are important tools in SQL. Choosing between them depends on whether uniqueness or performance is your priority.
- If your goal is to ensure clean, duplicate-free results — use UNION.
- If your focus is speed and completeness — go with UNION ALL.
Mastering when and how to use these operators efficiently can greatly improve query performance, simplify report generation, and make your database operations more effective.
SQL Server interview Questions Playlist: Click here to watch
SQL Server top 100 Interview Questions : Download PDF
