Summary: in this tutorial, you will learn how to use PostgreSQL UNION operator to combine result sets of multiple queries into a single result.
Introduction to PostgreSQL UNION operator
The UNION operator combines result sets of two or more SELECT statements into a single result set. The following illustrates the syntax of the UNION operator that combines result sets from two queries:
1 2 3 4 5 6 7 8 9 10 11 | SELECT column_1, column_2 FROM tbl_name_1 UNION SELECT column_1, column_2 FROM tbl_name_2; |
The following are rules applied to the queries:
- Both queries must return the same number of columns.
- The corresponding columns in the queries must have compatible data types.
The UNION operator removes all duplicate rows unless the UNION ALL is used.
The UNION operator may place the rows in the first query before, after or between the rows in the result set of the second query. To sort the rows in the combined result set by a specified column, you use the ORDER BY clause.
We often use the UNION operator to combine data from similar tables that are not perfectly normalized. Those tables are often found in the reporting or data warehouse system.
PostgreSQL UNION examples
Let’s take a look at the following tables:
- sales2007q1: stores sales data in Q1 2007.
- sales2007q2: stores sales data in Q2 2007.
sales2007q1 data:
sales2007q2 data:
PostgreSQL UNION example
We use the UNION operator to combine data from both tables as follows:
1 2 3 4 5 6 7 | SELECT * FROM sales2007q1 UNION SELECT * FROM sales2007q2; |
The query returns the following result:
PostgreSQL UNION ALL example
There are five rows in the combined result set because the UNION operator removes one duplicate row. To get all rows that include duplicate, you use the UNION ALL operator as follows:
1 2 3 4 5 6 7 | SELECT * FROM sales2007q1 UNION ALL SELECT * FROM sales2007q2; |
PostgreSQL UNION with ORDER BY example
To sort the combined result returned by the UNION operator, you use the ORDER BY clause. You need to put the ORDER BY clause at the last query as the following statement:
1 2 3 4 5 6 7 8 9 10 | SELECT * FROM sales2007q1 UNION ALL SELECT * FROM sales2007q2 ORDER BY name ASC, amount DESC; |
If you put the ORDER BY clause at the end of each query, the combined result set will not be sorted as you expected. Because when UNION operator combines the sorted result sets from each query, it does not guarantee the order of rows in the final result set.
In this tutorial, we have shown you how to use the PostgreSQL UNION and UNION ALL to combine the result sets from multiple queries into a single result set.