PostgreSQL Tutorial: UNION: Combining Result Sets of Multiple Queries

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.