PostgreSQL offers UNION and UNION ALL operators that combine/merge the result sets of at least two SELECT queries. Both these operators are responsible for combining the result set of different queries into one table. However, the behavior of both these operators is different, i.e., UNION combines only distinct records, while UNION ALL combines all records, including duplicates.

This guide will demonstrate how to combine different result sets using the UNION ALL operator in Postgres. For this purpose, the below-mentioned concepts will be discussed in this write-up:

• Creating Sample Tables
• Understanding Postgres UNION Operator
• How to Use the UNION ALL Operator in Postgres?

So, let’s begin!

Creating Sample Tables

Firstly we will create a couple of sample tables named “book_details” and “top_selling_books” using the CREATE TABLE statement:

CREATE TABLE book_details(
b_id INT PRIMARY KEY,
b_name TEXT
);

Now, we will use the INSERT INTO command to insert data into the newly created "book_details" table:

INSERT INTO book_details(b_id, b_name)
VALUES (1, 'The Great Gatsby'),
(2, 'The Picture of Dorian Gray'),
(3, 'Great Expectations'),
(4, 'Wuthering Heights'),
(5, 'The Kite Runner'),
(6, 'The Catcher in the Rye'),
(7, 'The Lord of the Rings'),
(8, 'His Dark Materials'),
(9, 'To Kill a Mockingbird'),
(10, 'The Grapes of Wrath'),
(11, 'Frankenstein'),
(12, 'Think and Grow Rich');

In the above code snippet, twelve records have been inserted into the book_details table using the INSERT query.

Let’s create one more table named “top_selling_books” using the CREATE TABLE command:

CREATE TABLE top_selling_books(
b_id INT PRIMARY KEY,
b_name TEXT);

Now, we will execute the INSERT query to insert data into the newly created "book_details" table:

INSERT INTO top_selling_books(b_id, b_name)
VALUES
(2, 'The Picture of Dorian Gray'),
(3, 'Great Expectations'),
(4, 'Little Women'),
(5, 'Charlotte's Web'),
(6, 'The Catcher in the Rye'),
(7, 'The Lord of the Rings'),
(10, 'The Grapes of Wrath'),
(11, 'Frankenstein'),
(12, 'Think and Grow Rich');

The above snippet indicates that nine records have been inserted into the “top_selling_books” table using the INSERT query.

Before moving towards the UNION ALL operator, firstly, we will comprehend the working of the UNION operator.

Understanding Postgres UNION Operator

To get the services of the UNION operator, users must follow the below syntax:

SELECT column_list_1
FROM tab_1
UNION
SELECT column_list_2
FROM tab_2;

To combine the result sets of table_1 and table_2, the UNION operator is used in the above syntax.

Example: How the UNION Operator Works in Postgres?

In this example, we will use the UNION operator to combine the result sets of book_details and top_selling_books:

SELECT b_name
FROM book_details
UNION
SELECT b_name
FROM top_selling_books;

From the output, you can observe that the result sets of the “book_details” and “top_selling_books” have been combined successfully. However, the resultant table contains only unique records.

How to Use Postgres UNION All Operator?

To include the duplicates in the resultant table, users must use the UNION ALL operator instead of the UNION operator:

SELECT column_list_1
FROM tab_1
UNION ALL
SELECT column_list_2
FROM tab_2;

The syntax mentioned above will be used to combine the result set of table_1 with the result set of table_2, including the duplicates.

Key Points:

The below-listed points explain the concept of UNION ALL operator in more detail:

- While using the UNION ALL operator, both SELECT statements must contain the same number of expressions/columns.
- Use the WHERE clause and the UNION ALL operator to combine the filtered data(based on some particular condition).
- Use the ORDER BY clause with the UNION ALL operator to sort the data of the resultant table in a specific format, i.e., ASC or DESC.
- The resultant table will have the same column names as the first SELECT statement.

Example 1: How Does the UNION ALL Operator Work in Postgres?

Execute the following query to combine the result sets of the “top_selling_books” and “book_details” tables:

SELECT b_name
FROM book_details
UNION ALL
SELECT b_name
FROM top_selling_books;

The output snippet proves that the UNION ALL operator retrieves a total of twenty-one records, including duplicates.

Example 2: How to Sort the Result Set of UNION ALL Operator in Descending Order?

Now, using the ORDER BY clause in conjunction with the UNION ALL operator, we will sort the result set retrieved by the UNION ALL operator as follows:

SELECT b_id, b_name
FROM book_details
UNION ALL
SELECT b_id, b_name
FROM top_selling_books
ORDER BY b_id DESC;

The result set will be sorted based on the “b_id” column:

The output proves that the ORDER BY clause sorted the resultant table in descending order.

That’s all from this Postgres guide!

Conclusion

In Postgres, the UNION ALL operator combines the result sets of two or more queries into one table, including the duplicate records. While using the UNION ALL operator, both SELECT statements must contain the same number of expressions/columns. The resultant table will have the same column names as the first SELECT statement. This Postgres blog went through various examples to explain the working of the Postgres UNION ALL operator.