When working with SQL, there are times when we need to combine results from multiple queries into a single result set. One of the simplest and most efficient ways to do this is by using UNION ALL. In this article, I will explain how UNION ALL works in SQL and provide some of the best UNION ALL examples.
What Is UNION ALL in SQL?
UNION ALL is a SQL set operator that allows us to combine the results of two or more SELECT statements. Unlike the standard UNION operator, which removes duplicate rows, UNION ALL retains all rows, including duplicates.
Syntax of UNION ALL
The syntax for using UNION ALL is straightforward:
SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2;
Each SELECT statement must have the same number of columns, with matching data types in the same order.
Key Differences Between UNION and UNION ALL
It is important to understand the differences between UNION and UNION ALL to choose the right one for your needs.
| Operator | Duplicates | Performance |
|---|---|---|
| UNION | Removes duplicates | Slower due to extra processing |
| UNION ALL | Keeps duplicates | Faster |
Best UNION ALL Examples
To demonstrate how UNION ALL works in SQL, let’s explore some examples.
Example 1: Merging Two Tables
Imagine you have two tables storing employee data and contractor data. You want to retrieve everyone’s name and role.
SELECT name, role FROM employees
UNION ALL
SELECT name, role FROM contractors;
This will return all employees and contractors, including duplicate names if they appear in both tables.
Example 2: Combining Data with Different Filters
Suppose you want to retrieve all employees who either belong to the Sales department or have been hired after 2020.
SELECT id, name FROM employees WHERE department = 'Sales'
UNION ALL
SELECT id, name FROM employees WHERE hire_date > '2020-01-01';
UNION ALL is useful here because an employee might satisfy both conditions and should appear twice.
Example 3: Using UNION ALL with Aggregations
You can also combine data from different aggregations. Let’s say you have two queries: one showing the count of employees by department and another by job title.
SELECT department AS category, COUNT(*) AS total FROM employees GROUP BY department
UNION ALL
SELECT job_title AS category, COUNT(*) AS total FROM employees GROUP BY job_title;
This query combines department-wise and job-title-wise counts into one dataset.
When to Use UNION ALL?
There are specific scenarios where UNION ALL is the best choice:
- When you don’t need to remove duplicates
- When performance is a concern and you want to avoid the extra processing cost of
UNION - When duplicate data is meaningful for your reporting or analysis
Performance Considerations
Since UNION performs additional sorting to remove duplicates, UNION ALL is significantly faster, especially for large datasets. If duplicates are not an issue in your reports, always prefer UNION ALL to improve query performance.
Conclusion
Understanding how UNION ALL works in SQL is crucial for efficiently combining query results. It is the best choice when keeping all data—including duplicates—is necessary, and it provides better performance than UNION. I hope these examples help clarify when and how to use UNION ALL in your SQL queries.
Other interesting article:
How UNION works in SQL? Best UNION examples