MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER
UNION

SQL UNION Operator: How To Combine Result Sets

intro

Find out everything you need to know about the SQL UNION operator, from syntax to use cases, and learn how it differs from UNION ALL.

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

The concept of union involves merging the elements of several sets into a single one. This common and logical operation is essential in so many real-life scenarios and applications that databases could not ignore it. This is why most DBMSs offer the SQL UNION operator! Just like union in set theory, UNION in SQL allows you to combine the result sets of separate SELECT queries.

In this article, you will learn what UNION is, how it works, how to use it, and in which scenarios.

Time to become a master at combining result sets in SQL!

SQL UNION: What is It?

In standard SQL, the UNION operator combines the results of two or more [SELECT statements] into a single result set. In other words, the UNION SQL operator merges the results of multiple queries into a single result set if they have the same structure. Thus, the result sets to be merged must have the same number of
columns and the corresponding columns must have compatible data types.

When referring to the SQL UNION operator, there are two options:

  • UNION: Eliminates duplicate rows from the combined result set by default.
  • UNION ALL: includes duplicate rows in the combined result set.

When to use one or the other depends on the specific use case.

How the UNION Operator Works

UNION in SQL works similarly to the union operation in the set theory. In mathematical set theory, the union of two sets—let's call them A and B—results in a new set containing all the distinct elements that are present in set A, set B, or both.

These are the steps required by the SQL UNION operator:

  1. The DBMS executes the different SELECT statements in the UNION query.
  2. The result sets are combined into a single result set.
  3. All duplicates are removed from the final result.
  4. If there is an ORDER BY clause at the end of the UNION query, the rows in the combined result set are sorted according to the specified condition. Otherwise, the rows in the final result set may not follow a specific order.

The UNION ALL variant works the same way but does not perform the third step.

To better understand how UNION works, consider an example. Assume you have two tables, students and teachers, each with columns name and age.

This is what the students table looks like:

Copy
        
1 | name | age | 2 |---------|-----| 3 | Alice | 20 | 4 | Bob | 22 | 5 | Charlie | 21 | 6 | Max | 40 |

And this is what the teachers table contains:

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

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

Copy
        
1 | name | age | 2 |---------|-----| 3 | Alice | 20 | 4 | Bob | 22 | 5 | Charlie | 21 | 6 | Max | 40 | 7 | David | 35 | 8 | Alice | 40 | 9 | Emma | 30 |

Notice that duplicate rows, such as the entry for Max, have been removed in the final result. This behavior is consistent with the mathematical concept of sets, where duplicates are not included in the union.

The UNION ALL result set would instead be:

Copy
        
1 | name | age | 2 |---------|-----| 3 | Alice | 20 | 4 | Bob | 22 | 5 | Charlie | 21 | 6 | Max | 40 | 7 | David | 35 | 8 | Alice | 40 | 9 | Emma | 30 | 10 | Max | 40 |

Here, no duplicates have been removed.

In summary, this is how UNION and UNION ALL visually work in SQL:

UNION vs UNION ALL visually
UNION vs UNION ALL visually

Time to see how to use UNION in SQL!

How to Use UNION in SQL

UNION is part of the ISO/ANSI specification, and the great majority of databases implement it the same way. This means that the SQL UNION syntax is pretty much the same on all DBMS technologies.

That is how you can combine the result sets of two SELECT statements with UNION in SQL:

Copy
        
1 SELECT column_1, column_2, ... 2 FROM table_1 3 [WHERE ...] 4 UNION 5 SELECT column_1, column_2, ... 6 FROM table_2 7 [WHERE ...] 8 [GROUP BY ...]

The SQL UNION ALL syntax is very similar:

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

The main difference is that the result set of this query will also contain duplicate rows.

If the column names have different names across SELECT statements, the result set will have the column names of the first SELECT statement. Consider for example this query:

Copy
        
1 SELECT id, email FROM customers 2 UNION 3 SELECT id, name FROM products

Here is what it would produce:

Note the column names in the result set
Note the column names in the result set

Notice how the resulting table has the same column names as the first SELECT.

Note: SELECT statements in a UNION query cannot have the ORDER BY clause. Instead, you can specify the sort conditions in an optional GROUP BY clause at the end of the query. This must reference the column names of the first SELECT statement.

UNION and UNION ALL operations are successful when these conditions are met:

  1. Every SELECT statement within UNION has the same number of columns.
  2. The selected columns have compatible data types.

If the first condition is not met, the UNION query will fail with the following error:

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

Otherwise, if the second condition is not met, the error message will be:

Copy
        
1 UNION types <type_1> and <type_2> cannot be matched

Note: Two data types are considered compatible for UNION when the DBMS can automatically convert one type to another. The resulting data type will usually be the least restrictive one. Check out the official documentation to see how your specific DMBS handles implicit casting.

You can also apply the UNION and UNION ALL SQL operators on multiple SELECT statements:

Copy
        
1 SELECT column_1, ..., column_N 2 FROM table_1 3 [WHERE ...] 4 UNION [ALL] 5 [...] 6 SELECT column_1, ..., column_N 7 FROM table_i 8 [WHERE ...] 9 UNION [ALL] 10 [...] 11 UNION [ALL] 12 SELECT column_1, ..., column_N 13 FROM table_m 14 [WHERE ...] 15 [GROUP BY ...]

In this case, each SELECT must respect the aforementioned conditions.

UNION SQL Use Cases

See two of the most common real-world use cases where the SQL UNION operator proves essential.

Combining Data From Similar Tables

When a table contains millions of rows, you might end up partitioning it logically across several tables. For example, consider a scenario where you have separate tables for sales data made in a specific year (e.g., sales_2022, sales_2022, sales_2024, …).

Thanks to UNION ALL, you can combine that data as if it were in a single table. That is useful for analysis, reporting, or visualization purposes:

Copy
        
1 SELECT * FROM sales_2022 2 UNION ALL 3 SELECT * FROM sales_2023 4 UNION ALL 5 SELECT * FROM sales_2024;

In this case, you should use UNION ALL, as different sales transactions in different years may have the same data.

Merging Data from Different Sources

UNION can be used to merge data from different sources—such as tables of different databases—into a single coherent dataset.

For instance, suppose you have customer information stored in both a CRM system and an e-commerce platform. You could combine this data into a unified customer profile with:

Copy
        
1 SELECT customer_id, name, email 2 FROM crm_db.customers 3 UNION 4 SELECT customer_id, full_name, email_address 5 FROM ecommerce_db.customers;

Best Practices of the SQL UNION Operator

To use the UNION SQL operator like a pro, keep in mind these tips:

  • Ensure that the number of columns selected by each SELECT statement is the same.
  • Use UNION ALL instead of UNION if you know that there are no duplicate rows or if you want to include duplicates. UNION ALL is generally faster than UNION because it does not perform the extra step of removing duplicates.
  • Always test the performance impact of using UNION, especially when dealing with large datasets. Sometimes, alternative approaches like INs, JOINs, or SQL subqueries might be more efficient.
  • Be cautious when combining data from different sources, as their schemas might not perfectly align. Ensure that the columns being merged have compatible data types.
  • Keep in mind that the order of rows in the final result set might not be guaranteed unless you explicitly use an ORDER BY clause at the end of the UNION operation.

Conclusion

In this guide, you understood what the UNION operator in SQL is, how it differs from UNION ALL, and how they work. You now know that UNION in SQL is the implementation of the union operation in set theory. Specifically, it enables you to combine two or more result sets into a single result set. Thanks to the use cases and tips presented here, you have learned how to use it in real-world scenarios.

UNION is one of those complex operators that require a full-featured database client. The best one based on user satisfaction? DbVisualizer! This powerful database client supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free!

FAQ

SQL UNION vs UNION ALL: What is the difference?

The main difference in the SQL UNION vs UNION ALL comparison is that UNION eliminates duplicate rows from the combined result set. On the contrary, UNION ALL includes all rows from each SELECT statement, regardless of duplicates. As a result, UNION ALL is generally faster than UNION because it does not need to perform the additional step of checking for and removing duplicates.

SQL UNION vs JOIN: What is the difference?

The SQL UNION vs JOIN comparison is based on the way the two operators combine data sets. UNION stacks data vertically, while JOIN merges data horizontally. In essence, UNION combines rows from different tables, while JOIN combines columns from different tables based on a common key.

How to write an SQL UNION ORDER BY query?

To write an SQL UNION ORDER BY query, you must first perform the UNION operation to combine the results of multiple SELECT queries. Then, you add the ORDER BY clause at the end of the entire UNION query to specify the sorting order for the final result set. For example:

Copy
        
1 SELECT name, age FROM employees 2 UNION 3 SELECT full_name, age FROM contractors 4 ORDER BY age, name;

This query combines the employees and contractors records based on the name and age columns, Then, it orders the final result set by age and name. Note that the column names in the ORDER BY query match the column names in the first SELECT statement.

What are some common SQL injection UNION attacks?

Common SQL injection UNION attacks involve injecting malicious SQL code into input fields of web applications to manipulate database queries. Attackers often attempt to exploit vulnerabilities by inserting a UNION operator followed by another SELECT statement to retrieve additional data from the database. For example, attackers may inject code such as UNION SELECT username, password FROM users to extract sensitive information like usernames and passwords from a query that normally retrieves other data. Learn how to prevent SQL injection attacks.

Is it possible to UNION a table with itself in SQL?

Yes, it is possible to UNION a table with itself in SQL. This can be useful for removing duplicate rows of a table in the result set or when you want to combine rows from different subsets of the same table. For example:

Copy
        
1 SELECT * FROM products 2 WHERE category = "electronics" 3 UNION 4 SELECT * FROM products 5 WHERE category = "shoes"

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 ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
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 ↗