How to Use the PostgreSQL UNION & UNION ALL Operators: Easy Steps and Examples Explained

PostgreSQL is one of the most robust Relational Database Management Systems. You can use SQL to query your data stored in the database. While querying, there might be a situation where you would need to view similar columns from multiple columns. This is where you can use UNION & UNION ALL Operators in PostgreSQL.

In this article, you will come to know about PostgreSQL UNION & UNION ALL Operators along with a wide range of use cases.

What is PostgreSQL UNION Operator?

The PostgreSQL UNION operator is used to combine results from many SELECT statements into a result set. If the result of the SELECT statements has any duplicate rows, then those rows won’t be displayed in the result set.

The logical meaning of the UNION Operator in PostgreSQL depicts the following Venn diagram:

1) Syntax

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

OR

SELECT select_list_1
FROM table_expresssion_1
UNION
SELECT select_list_2
FROM table_expression_2

2) Rules & Guidelines

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

• The number of columns returned by both queries must be the same.
• The data types of the respective columns in the queries must be compatible.

PostgreSQL UNION vs UNION ALL Operator: Key Difference

Feature	UNION Operator	UNION ALL Operator
Functionality	Combines rows from multiple tables or queries. Removes duplicate rows.	Combines rows from multiple tables or queries, including duplicates.
Duplicates	Does not display duplicate rows.	Displays duplicate rows from all tables.
Performance	Slower, as it involves checking and removing duplicates.	Faster, as no duplicate removal is required.
Use Case	Use when you need unique rows from combined results.	Use when you need all rows, including duplicates.

The UNION Operator is used to extract rows that are being specified in a query from multiple tables. If both the tables have exactly the same rows, then it won’t display the repeated values. While, in the case of the UNION ALL operator, it works the same as the UNION Operator but it also displays the duplicates (repeated values) from all the tables.

PostgreSQL UNION & UNION ALL Operators: Prerequisites

Before learning about the use cases of the UNION & UNION ALL operator in PostgreSQL, you first need to create a table and feed data into the table.

1) Creating Sample Tables

Below is the sample code you can use to create tables on which you can write queries using the UNION operator in PostgreSQL.

CREATE TABLE sales_2021_q1(
    id SERIAL,
    product VARCHAR(50) NOT NULL,
    Top_client VARCHAR(50),
    PRIMARY KEY (id)
);
CREATE TABLE sales_2021_q2(
    id SERIAL,
    product VARCHAR(50) NOT NULL,
    Top_client VARCHAR(50),
    PRIMARY KEY (id)
);

2) Inserting Data

Below is the sample code you can use to insert data into the tables.

INSERT INTO sales_2021_q1(product, Top_client)
VALUES
    ('Dove Shampoo', 'X'),
    ('Ponds Cream', 'Z'),
    ('Amul Chocominis', 'F'),
    ('King Pickle', 'A');

INSERT INTO sales_2021_q2(product, Top_client)
VALUES
    ('Hajmola', 'A'),
    ('Parle-G', 'B'),
    ('Amul Chocomonis', 'F');

PostgreSQL UNION & UNION ALL Operator: Example Queries

Let’s have a look at the following example queries which showcase the usage of the UNION & UNION ALL operators in PostgreSQL.

1) PostgreSQL UNION: Example 1

SELECT * FROM sales_2021_q1 
UNION
SELECT * FROM sales_2021_q2;

Output:

 id |     product     | top_client 
----+-----------------+------------
  1 | Dove Shampoo    | X
  2 | Parle-G         | B
  2 | Ponds Cream     | Z
  4 | King Pickle     | A
  1 | Hajmola         | A
  3 | Amul Chocominis | F
(6 rows)

In this case, using the PostgreSQL UNION operator, you can display the content of both the tables but the order in which the records will be displayed is random. In this case, there won’t be any duplicate value.

2) PostgreSQL UNION: Example 2

SELECT * FROM sales_2021_q1
UNION
SELECT * FROM sales_2021_q2
ORDER BY product;

Output:

id |     product     | top_client 
----+-----------------+------------
  3 | Amul Chocomonis | F
  1 | Dove Shampoo    | X
  1 | Hajmola         | A
  4 | King Pickle     | A
  2 | Parle-G         | B
  2 | Ponds Cream     | Z
(6 rows)

In this case, the resulting table will display all the fields of both tables without duplicate values. Along with that, since you’re using the “ORDER BY” clause, so the results will be displayed in ascending order of the product values.

3) PostgreSQL UNION ALL: Example 1

SELECT * FROM sales_2021_q1
UNION ALL
SELECT * FROM sales_2021;

Output:

id |     product     | top_client 
----+-----------------+------------
  1 | Dove Shampoo    | X
  2 | Ponds Cream     | Z
  3 | Amul Chocominis | F
  4 | King Pickle     | A
  1 | Hajmola         | A
  2 | Parle-G         | B
  3 | Amul Chocomonis | F
(7 rows)

In this case, the resulting table will display all the fields of both tables even if there are duplicate values. First, it will display all the contents of the first table followed by all the contents of the second table.

4) PostgreSQL UNION ALL: Example 2

SELECT * FROM sales_2021_q1
UNION ALL
SELECT * FROM sales_2021_q2
ORDER BY product;

Output:

id |     product     | top_client 
----+-----------------+------------
  3 | Amul Chocominis | F
  3 | Amul Chocomonis | F
  1 | Dove Shampoo    | X
  1 | Hajmola         | A
  4 | King Pickle     | A
  2 | Parle-G         | B
  2 | Ponds Cream     | Z
(7 rows)

In this case, the resulting table will display all the fields of both tables even if there are duplicate values. Since we’re using the “ORDER BY” clause here then all the records will be displayed according to the ascending order of the product values.

For further information about UNION Operator in PostgreSQL, you can visit the official website.

You can also take a look at how you can effortlessly work with PostgreSQL LEFT JOIN/LEFT OUTER JOIN to learn more about JOINS in PostgreSQL in detail.

Conclusion

This article illustrated the usage of the UNION & UNION ALL Operators in PostgreSQL. You had an in-depth understanding of the difference between the two operators and different queries to showcase their applications.

Now, you can move forward and use the UNION & UNION ALL Operators to combine the result of multiple tables.

Ingesting and transferring data into your desired warehouse using ETL for Data Analysis using PostgreSQL as your data source might be a time-consuming job. The situation becomes much more overwhelming when you consider how much money and resources are necessary to hire data engineers and analysts to make sense of this data.

However, with Hevo at your fingertips, you won’t have to worry about your PostgreSQL Data Replication demands. It will just take a few minutes to load your data into your chosen data warehouse.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions