In PostgreSQL, the UNION clause/operator is used to combine/merge the result set of at least two queries. The UNION clause/operator doesn’t return the duplicate rows in the combined set. The UNION ALL operator/clause is used to retain the duplicated rows.

This write-up will illustrate the working of the UNION operator/clause with the help of different examples. So let’s begin!

How to Use the UNION Clause/Operator in PostgreSQL?

The UNION operator assists us in combining the data of similar tables. The below-listed rules must be followed to avail the functionality of the UNION operator:

- The Column’s number and the column’s order must be the same for all the queries to be combined.

- The data type must be the same/compatible.

- The length of each result set can vary i.e. it’s not necessary to have the result set of the same length.

The syntax of the UNION operator will be as follows:

SELECT col_list_1
FROM tbl_expresssion_1
UNION
SELECT col_list_2
FROM tbl_expresssion_2

In this example, the col_list_1 and col_list_2 represent the column’s list to be selected from the respective tables. The UNION operator will be used between the queries to be combined.

Example: How to Combine Result Sets of Two SELECT Queries Using UNION Operator?

We already have two tables: article_details and recommended_articles. Let’s populate each table one by one:

SELECT * FROM article_details;

The SELECT statement successfully fetched all the columns of the article_details column.

Let’s run the below query to describe the rocommeded_articles table:

SELECT * FROM recommended_articles;

The output shows that the recommended_articles table has three records.

Let’s run the following query to combine two tables: article_details and recommended_article:

SELECT * FROM article_details
UNION
SELECT * FROM recommended_articles;

The UNION operator succeeded in combining the result sets of article_details and recommended_article. From the output, you can clearly observe that the UNION operator didn’t retain the duplicated rows in the resultant table.

How to Use the UNION Operator With ORDER BY Clause?

In the previous example, we witnessed that the table rows were out of order. You can specify the order of the table records by using the ORDER BY clause. The below snippet shows the updated syntax of the UNION operator:

SELECT col_list_1
FROM tbl_expresssion_1
UNION
SELECT col_list_2
FROM tbl_expresssion_2
ORDER BY col_name ASC|DESC;

Using the ORDER BY clause, you can sort the result set in ascending/descending order.

Example: How Does the ORDER BY Clause Work With the UNION Operator in PostgreSQL?

Let’s implement the below-given query to sort the combined result set in the ascending order:

SELECT * FROM article_details
UNION
SELECT * FROM recommended_articles
ORDER BY article_id ASC;

The output authenticates the working of ORDER BY clause and UNION operator. As we get the combined result set in ascending order(sorted according to article_id).

How Does the UNION ALL Clause/Operator Work in PostgreSQL?

The UNION ALL operator combines at least two tables and retains the duplicated rows. Let’s execute the following query to combine the article_details table with the recomended_articles table and retain the duplicated rows of both these tables:

SELECT * FROM article_details
UNION ALL
SELECT * FROM recommended_articles
ORDER BY article_id ASC;

The output shows that the UNION ALL operator/clause keeps the duplicated rows as well.

Conclusion

In PostgreSQL, the UNION clause/operator combines the result set of at least two queries. It doesn’t return the duplicated rows in the combined set. In Postgres, the UNION ALL operator is used to retain the duplicated rows. The ORDER BY clause can be used with the UNION and UNION ALL operators to sort the combined set in a particular order. This write-up went through different examples to explain the usage of UNION and UNION ALL operators in a better way.