intro
Learn more about the SQL UNION ALL operator, explore its syntax and use cases, and discover how and why it differs from UNION.
Combining data from different datasets is one of the most common operations in data management. In an SQL database, this means taking rows from one table and combining them with rows from another table. The fastest and most simple way to achieve that result is through the SQL UNION ALL operator.
In this article, you will find out what UNION ALL is, how it works, how to use it, and in which scenarios.
What Is the SQL UNION ALL Operator?
In standard SQL, UNION ALL is a variant of the UNION operator. By default, UNION combines the results of two or more [SELECT](https://www.dbvis.com/thetable/crud-advanced-select-queries/) queries and removes all duplicates from the final result. Instead, UNION ALL combines the rows of the result sets without removing duplicates. Thus, a row with the same data may appear multiple times in the result set of a SQL UNION ALL query.
Similar to UNION, the UNION ALL SQL operator requires that all the queries involved in the operation produce results with the same structure. Specifically, they must have the same number of columns and the corresponding columns must have compatible data types.
Since UNION ALL is defined in the standard SQL specification, all major DBMS technologies support it. Refer to the specific documentation pages for more specific information:
Let’s better understand how UNION ALL works with an example.
How the UNION ALL SQL Operator Works
In mathematical terms, the result of a UNION ALL SQL operation is a multiset. If you are not familiar with this concept, a multiset is a collection of elements where a single element can appear even multiple times. In a database, that collection is a table and its elements are the rows of the table. This means that UNION ALL produces tables that can contain duplicated rows.
To avoid confusion, keep in mind that in the context of databases the term "result set" refers to the collection of rows returned by a query, which may indeed contain duplicates. Thus, while "result set" originates from the concept of sets in mathematics, it does not strictly adhere to the mathematical definition of a set (which cannot contain duplicates by definition).
Now, take a look at what happens when an SQL UNION ALL query is performed:
To better understand how UNION ALL works, let’s consider an example. Suppose you have two tables with the same structure, customers and employees, and you want to combine them.
These are the rows contained by the customers table:
1
| name | age |
2
|---------|-----|
3
| Alice | 30 |
4
| Bob | 45 |
5
| Claire | 25 |
And this is what the employees table looks like:
1
| name | age |
2
|---------|-----|
3
| David | 35 |
4
| Alice | 30 |
5
| Emma | 40 |
The UNION ALL result set of the two tables would be:
1
| name | age |
2
|---------|-----|
3
| Alice | 30 |
4
| Bob | 45 |
5
| Claire | 25 |
6
| David | 35 |
7
| Alice | 30 |
8
| Emma | 40 |
Notice the presence of duplicate rows, such as the entries for Alice.
Great! You are ready to see how to use UNION ALL in SQL.
How to Use UNION ALL in SQL: Syntax and First Examples
As part of the ISO/ANSI specification, the SQL UNION ALL syntax is pretty much the same across all DMBS technologies. That is how you can combine the result sets of two SELECT statements with UNION ALL in SQL:
1
SELECT column_1, column_2, ...
2
FROM table_1
3
[WHERE ...]
4
UNION ALL
5
SELECT column_1, column_2, ...
6
FROM table_2
7
[WHERE ...]
8
[ORDER BY ...]
This is just an example, but you can easily extend this syntax to UNION ALL several SELECT statements.
According to standard SQL, the individual SELECT statements in a UNION ALL query cannot have an ORDER BY clause. If you do not respect that, you will get this SQL syntax error:
1
ERROR: syntax error at or near "UNION"
However, MySQL is non-standard and supports local ORDER BY clauses.
In general, you can add an optional GROUP BY clause at the end of the query to specify the sort conditions. This final clause must reference the column names of the first SELECT statement. Why? Because the result set will have the column names of the first SELECT statement in the UNION ALL SQL query.
Got lost? Take a look at this example:
1
SELECT email FROM customers
2
UNION ALL
3
SELECT name FROM products
4
ORDER BY email
To execute this query, the database will:
Running this query in a database client against real data would produce:
Note the combined result set
Notice how the resulting set is sorted alphabetically, has the same column names as the first SELECT, and contains duplicate rows.
While the column names in the SELECT statements can be different, there are some conditions that the SQL UNION ALL operator expects to be met. These are:
When the first condition is not met, the UNION ALL query will produce the following error:
1
The used SELECT statements have a different number of columns
Otherwise, when the second condition is not met, the query will fail with:
1
UNION ALL types <type_1> and <type_2> cannot be matched
Two data types are considered compatible for UNION ALL when they automatically be converted from one type to another. Usually, the resulting data type will be the less restrictive one. Consult the official documentation of your specific DBMS to see how it handles implicit casting.
SQL UNION ALL Example
The operator UNION ALL in SQL can be used in a variety of use cases, from combining data from different sources to generating an individual result set from tables in the same database. One of the most common scenarios in which UNION ALL proves effective is combining data from horizontally partitioned tables.
For example, suppose you have separate tabs for sales made on different continents (e.g., sales_america, sales_europe, sales_asia, ...). With UNION ALL, you can easily combine all this data into a single table:
1
SELECT date, amount, price FROM sales_america
2
UNION ALL
3
SELECT date, amount, price FROM sales_europe
4
UNION ALL
5
SELECT date, amount, price FROM sales_asia;
Preserving duplicates in this case is essential, since different sales transactions on different continents may contain the same detailed data. Excluding duplicates with a UNION query may in fact lead to incorrect reports or conclusions.
Conclusion
In this guide, you learned what the SQL UNION ALL operator is, why it differs from UNION, and how to use it in a query. You now know that UNION ALL in SQL combines two or more result sets into a single result set, without excluding duplicates.
UNION ALL is one of those advanced operators that require a full-featured database client. The best one based on user satisfaction? DbVisualizer! This powerful database client supports many DBMS technologies, can generate ERD-like schemas with a single click, and comes with advanced query optimization functionality. Try DbVisualizer for free!
FAQ
What is the SQL difference between UNION and UNION ALL?
The SQL difference between UNION and UNION ALL is that UNION eliminates duplicate rows from the combined result set, while UNION ALL combines all rows from every SELECT statement, including duplicates. Since UNION ALL does not have to perform uniqueness checks, it is generally faster than UNION.
Is the SQL Server UNION ALL operator available?
Yes, the SQL Server UNION ALL operator is available and works just as it does in other database systems, including MySQL and PostgreSQL. In other terms, SQL Server allows you to write UNION ALL SQL queries.
Should SQL UNION ALL statements be enclosed by parentheses?
Although it is possible to enclose each SELECT statically by prothesis, that is not required by the UNION ALL specification in the SQL standard. So, this query:
1
(SELECT name FROM customers
2
WHERE age > 30)
3
UNION ALL
4
(SELECT name FROM users)
and this query:
1
SELECT name FROM customers
2
WHERE age > 30
3
UNION ALL
4
SELECT name FROM users
are equivalent.
What is the difference between a UNION ALL SQL query and a JOIN query?
UNION ALL and JOIN are both SQL two operators to combine result sets. The main difference is that UNION stacks data vertically, while JOIN combines data horizontally. Therefore, UNION combines rows from different tables into a single dataset, while JOIN extends the rows of a table with extra columns from another table.
How to write an SQL UNION ALL ORDER BY query?
To write an SQL UNION ALL ORDER BY query in most DBMS technologies, you need to specify an ORDER BY clause at the end of the global UNION ALL query. This clause will define how to sort the rows in the result set, as in the example below:
1
SELECT full_name, age FROM employees
2
UNION ALL
3
SELECT name, age FROM users
4
ORDER BY age, full_name;
This query combines the records of the employees and users tables and then sorts the final result set by age and full_name. Note that the column names in the ORDER BY clause need to reference the column names in the first SELECT statement.
