SQL UNION

In this tutorial, we will learn how to use the SQL UNION clause to combine the result from two or more SELECT statement into a single result set.

The following illustration shows the overview of an SQL UNION query:

SQL UNION Syntax

The following shows the syntax for combining two select statements using a UNION clause:

SELECT
col_1,
col_2,
...col_N
FROM
tbl_1
UNION

SELECT
col_1,
col_2,
...col_N
FROM
tbl_2;

Before performing a union query, the following requirements must be met:

The number of columns in each select statement must be identical.
The column in the same position in each select statement must be of similar data type.
The order of the columns must be correct in all select statements.

Let us illustrate how we can use a UNION query with an actual table.

Table 1:

The following shows the columns and data in the first table:

id|server_name |address |installed_version|
--+-------------+--------------+-----------------+
1|SQL Server |localhost:1433|15.0 |
2|Elasticsearch|localhost:9200|8.4.3 |
3|Redis |localhost:6379|6.0 |
4|PostgreSQL |localhost:5432|14.5 |

Table 2:

The structure and records of the second table are as shown in the following:

id|tool |version|licensed |
--+----------------------------+-------+----------+
1|SQL Server Management Studio|18.0 |commercial|
2|Kibana |7.17.7 |free |
3|DBeaver |22.2 |Enterprise|
4|DataGrip |2022.2 |Commercial|

SQL UNION Tables

We can perform a UNION operation on the values of both tables as shown in the following query:

SELECT
SERVER_NAME,
INSTALLED_VERSION
FROM
STACK_MAPPING
UNION
SELECT
TOOL,
VERSION
FROM
CONNECTOR;

This should combine the queries and return a table as follows:

server_name |installed_version|
----------------------------+-----------------+
SQL Server |15.0 |
Elasticsearch |8.4.3 |
Redis |6.0 |
PostgreSQL |14.5 |
SQL Server Management Studio|18.0 |
Kibana |7.17.7 |
DBeaver |22.2 |
DataGrip |2022.2 |

Conclusion

This article provides the basics of working with the UNION clause in SQL to combine the results of two or more SELECT statements. Feel free to check the other tutorials for more.