MySQL
ORACLE
POSTGRESQL
SQL
UNION

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

intro

Learn more about the SQL UNION ALL operator, explore its syntax and use cases, and discover how and why it differs from UNION.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

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:

  1. The DBMS executes all the queries in the UNION ALL SQL query.
  2. Rows from the different result sets are combined into a single result set.
  3. If there is an ORDER BY clause at the end of the UNION ALL query, the rows of the combined result set are sorted according to the specified condition. Otherwise, the rows of the final result set may or may not follow a specific order depending on the DBMS in use.

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:

Copy
        
1 | name | age | 2 |---------|-----| 3 | Alice | 30 | 4 | Bob | 45 | 5 | Claire | 25 |

And this is what the employees table looks like:

Copy
        
1 | name | age | 2 |---------|-----| 3 | David | 35 | 4 | Alice | 30 | 5 | Emma | 40 |

The UNION ALL result set of the two tables would be:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
1 SELECT email FROM customers 2 UNION ALL 3 SELECT name FROM products 4 ORDER BY email

To execute this query, the database will:

  1. Select the requested data from the products table.
  2. Select the data from the customers table.
  3. Combine the two result sets into a single result set, with the column names of the first result set.
  4. Sort the result set based on the conditions specified in the ORDER BY clause.

Running this query in a database client against real data would produce:

Note the combined result set
Note the combined result set

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:

  1. Every SELECT statement within UNION ALL must select the same number of columns.
  2. The corresponding selected columns must have compatible data types.

When the first condition is not met, the UNION ALL query will produce the following error:

Copy
        
1 The used SELECT statements have a different number of columns

Otherwise, when the second condition is not met, the query will fail with:

Copy
        
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:

Copy
        
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:

Copy
        
1 (SELECT name FROM customers 2 WHERE age > 30) 3 UNION ALL 4 (SELECT name FROM users)

and this query:

Copy
        
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:

Copy
        
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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL INTERSECT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 2 min 2024-05-23
title

SQL Formatter: Definition and Best Tools

author Antonello Zanini tags SQL 9 min 2024-05-20
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06
title

SQL CONVERT: The Handbook of Data Conversion in SQL

author Antonello Zanini tags CONVERT Data Conversion MySQL SQL SERVER 7 min 2024-05-02
title

SQL CASE Statement: Definitive Guide

author Antonello Zanini tags CASE ORACLE POSTGRESQL SQL SQL SERVER 5 min 2024-04-29
title

SQL REPLACE Function: A Comprehensive Guide

author TheTable tags SQL 5 min 2024-04-25
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

SQL EXCEPT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 7 min 2024-04-18

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗