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.
Table of Contents
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:
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.
The 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 UNION
works:
PostgreSQL UNION with ORDER BY clause
The 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: top_rated_films
and 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 top_rated_films
table:
SELECT * FROM top_rated_films;
The following statement returns the data from the most_popular_films
table:
SELECT * FROM most_popular_films;
PostgreSQL UNION examples
Let’s take some examples of using the PostgreSQL UNION
operator.
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 top_rated_films
and most_popular_films
tables:
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.
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, you have learned how to use the PostgreSQL UNION
and UNION ALL
to combine the result sets from multiple queries into a single result set.