PostgreSQL UNION operator (original) (raw)

Last Updated : 12 Jul, 2025

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 `

CREATE DATABASE sales2020; CREATE TABLE sales2020q1( id SERIAL, name VARCHAR(50) NOT NULL, amount VARCHAR(50), PRIMARY KEY (id) ); CREATE TABLE sales2020q2( id SERIAL, name VARCHAR(50) NOT NULL, amount VARCHAR(50), PRIMARY KEY (id) ); INSERT INTO sales2020q1(name, amount) VALUES ('Raju Kumar', '121232'), ('Nikhil Aggarwal', '254789'), ('Abhishek Kelenia', '365487');

INSERT INTO sales2020q2(name, amount) VALUES ('Raju Kumar', '457264'), ('Nikhil Aggarwal', '365241'), ('Abhishek Kelenia', '759864');

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:

PostgreSQL UNION operator Example

**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:

PostgreSQL UNION operator Example

**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 **SELECTstatement.

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.