August 3, 2023
Summary: in this tutorial, you will learn how to use PostgreSQL UNION operator to combine result sets of multiple queries into a single result sets.
Introduction to PostgreSQL UNION operator
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:
SELECT select_list_1 FROM table_expresssion_1 UNION SELECT select_list_2 FROM table_expression_2
To combine the result sets of two queries using the
UNION operator, the queries must conform to the following rules:
- The number and the order of the columns in the select list of both queries must be the same.
- The data types must be compatible.
UNION operator removes all duplicate rows from the combined data set. To retain the duplicate rows, you use the the
UNION ALL instead.
The following Venn digram illustrates how the
PostgreSQL UNION with ORDER BY clause
UNION operator may place the rows from the result set of the first query before, after, or between the rows from the result set of the second query.
To sort rows in the final result set, you use the
ORDER BY clause in the second query.
In practice, you often use the
UNION operator to combine data from similar tables, which are not perfectly normalized, in the data warehouse or business intelligence systems.
Setting up sample tables
The following statements create two tables:
most_popular_films, and insert data into these tables:
DROP TABLE IF EXISTS top_rated_films; CREATE TABLE top_rated_films( title VARCHAR NOT NULL, release_year SMALLINT ); DROP TABLE IF EXISTS most_popular_films; CREATE TABLE most_popular_films( title VARCHAR NOT NULL, release_year SMALLINT ); INSERT INTO top_rated_films(title,release_year) VALUES ('The Shawshank Redemption',1994), ('The Godfather',1972), ('12 Angry Men',1957); INSERT INTO most_popular_films(title,release_year) VALUES ('An American Pickle',2020), ('The Godfather',1972), ('Greyhound',2020);
The following shows the data from the
SELECT * FROM top_rated_films;
The following statement returns the data from the
SELECT * FROM most_popular_films;
PostgreSQL UNION examples
Let’s take some examples of using the PostgreSQL
1) Simple PostgreSQL UNION example
The following statement uses the
UNION operator to combine data from both tables:
SELECT * FROM top_rated_films UNION SELECT * FROM most_popular_films;
The query returns the following result:
The result set includes five rows in the result set because the
UNION operator removes one duplicate row.
2) PostgreSQL UNION ALL example
The following statement uses the
UNION ALL operator to combine result sets from the
SELECT * FROM top_rated_films UNION ALL SELECT * FROM most_popular_films;
In this example, the duplicate row is retained in the result set.
3) PostgreSQL UNION ALL with ORDER BY clause example
To sort the result returned by the
UNION operator, you place the
ORDER BY clause end of the last query like this:
SELECT * FROM top_rated_films UNION ALL SELECT * FROM most_popular_films ORDER BY title;
If you place the
ORDER BY clause at the end of each query, the combined result set will not be sorted as you expected.
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, you have learned how to use the PostgreSQL
UNION ALL to combine the result sets from multiple queries into a single result set.