PostgreSQL - UNION operator

The PostgreSQL UNION operator is a powerful tool used to combine result sets from multiple queries into a single result set. It helps in consolidating data from different sources, making it easier to analyze and report.

From this article, we can better understand the UNION Operator in PostgreSQL

Syntax

SELECT
 column_1,
 column_2
FROM
 table_name_1
UNION
SELECT
 column_1,
 column_2
FROM
 table_name_2;

Rules for Using UNION

The below rules need to be followed while using a UNION operator:

• Both queries must return the same number of columns.
• The corresponding columns in the queries must have compatible data types.

Note: The UNION operator removes all duplicate rows from the query set.

Setting Up Sample Tables

Let's look into some examples of the UNION operator by setting up two sample tables in a sample database(say, sales2020). Let's say table 'sales2020q1' represents the sales of a particular product in the first quarter of 2020 and 'sales2020q2' represents the sales in the second quarter of the same year.

Now let's set up the database following the below procedures. Create the 'sales2020' database using the below command:

PostgreSQL UNION operator Examples

Now that our sample database is ready. Let's implement the UNION operator in a few examples.

Example 1: Combining Data from Two Tables

Here we will use the UNION operator to combine data from both 'sales2020q1' and 'salese2020q2' tables.

Query:

SELECT * FROM
 sales2020q1
UNION
SELECT * FROM
 sales2020q2;

Output:

Explanation: This query combines the rows from both tables into a single result set, removing duplicate rows.

Example 2: Sorting the Combined Results

Here we will sort the combined result returned by the UNION operator in defending order of "id" by using the ORDER BY clause after combining the data from both 'sales2020q1' and 'salese2020q2' tables.

SELECT * FROM
 sales2020q1
UNION ALL
SELECT * FROM
 sales2020q2
ORDER BY 
 name ASC,
 amount DESC;

Output:

Explanation: This query combines the rows from both tables without removing duplicates (UNION ALL), and sorts the results as specified.

Important Points About PostgreSQL UNION Operator

• The column names in the result set of a UNION operation are taken from the first SELECT statement.
• Using UNION instead of UNION ALL can be slower because UNION removes duplicates by sorting and then eliminating duplicates, which can be computationally intensive.
• Be aware that NULL values are treated as equal when removing duplicates in a UNION operation. Thus, rows with NULL values in the same positions will be considered duplicates.
• UNION removes duplicate rows. However, if duplicates are essential for your use case, use UNION ALL to retain all rows.