PostgreSQL UNION & UNION ALL Operators Made Easy | A 101 Guide

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

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_q2;

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 here.

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.

Want to explore more about different clauses and statements while writing queries and creating tables in PostgreSQL? You can go through these articles.

• Working with PostgreSQL Subquery | A 101 Guide
• PostgreSQL LEFT JOIN/ LEFT OUTER JOIN: Syntax, Working & Examples

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.

Hevo’s strong integration with 150+ Data Sources (including 40+ Free Sources) like PostgreSQL, you can export data from your selected data sources and load it to the destination of your choice. It also assists you with reorganizing and enhancing your data so that it is ready for analysis. Now, you can readily save your time and focus on gaining insights and doing in-depth research on your data using BI solutions.

You can now replicate your PostgreSQL data to any data warehouse of your choice, including Amazon Redshift, Snowflake, Google BigQuery, and Firebolt.

Why don’t you give Hevo a try? Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and make a decision on your best-suited plan. 

Share your thoughts on learning about UNION & UNION ALL Operators in PostgreSQL in the comments section below. If you have any questions, do let us know. We’d be happy to help.

Manisha Jena Research Analyst, Hevo Data

Manisha Jena is a data analyst with over three years of experience in the data industry and is well-versed with advanced data tools such as Snowflake, Looker Studio, and Google BigQuery. She is an alumna of NIT Rourkela and excels in extracting critical insights from complex databases and enhancing data visualization through comprehensive dashboards. Manisha has authored over a hundred articles on diverse topics related to data engineering, and loves breaking down complex topics to help data practitioners solve their doubts related to data engineering.