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.
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:
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:
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:
1
| name | age |
2
|---------|-----|
3
| Alice | 20 |
4
| Bob | 22 |
5
| Charlie | 21 |
6
| Max | 40 |
And this is what the teachers table contains:
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:
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:
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:
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:
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:
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:
1
SELECT id, email FROM customers
2
UNION
3
SELECT id, name FROM products
Here is what it would produce:
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:
If the first condition is not met, the UNION query will fail with the following error:
1
The used SELECT statements have a different number of columns
Otherwise, if the second condition is not met, the error message will be:
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:
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:
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:
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:
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:
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:
1
SELECT * FROM products
2
WHERE category = "electronics"
3
UNION
4
SELECT * FROM products
5
WHERE category = "shoes"
