PostgreSQL Union, Union ALL with Examples

Details

What is PostgreSQL Union?

The PostgreSQL UNION operator is used for combining result sets from more than one SELECT statement into one result set. Any duplicate rows from the results of the SELECT statements are eliminated. The UNION operator works under two conditions:

  • The SELECT queries MUST return a similar number of queries.
  • The data types of all corresponding columns must be compatible.

The UNION operator is normally used to combine data from related tables that have not been normalized perfectly.

In this PostgreSQL tutorial, you will learn:

Syntax

SELECT expression_1, expression_2, ... expression_n
FROM tables
[WHERE condition(s)]
UNION
SELECT expression_1, expression_2, ... expression_n
FROM tables
[WHERE condition(s)];

Here is an explanation for the above parameters:

The expression_1, expression_2, ... expression_n are the calculations or columns that you need to retrieve.

The tables are the tables from which you need to retrieve records.

The WHERE condition(s) are the conditions that must be met for records to be retrieved.

Note: that since the UNION operator doesn't return duplicates, the use of UNION DISTINCT will have no impact on the results.

Union

The UNION operator removes duplicates. Let us demonstrate this.

We have a database named Demo with the following tables:

Book:

Price:

Let us run the following command: 

SELECT id
FROM Book
UNION
SELECT id
FROM Price;

The command will return the following:

The id column appears in both the Book and the Price tables. However, it appears only once in the result. The reason is that PostgreSQL UNION operator doesn't return duplicates.

Union All

This operator combines result sets from more than one SELECT statement without removing duplicates. The operator requires each SELECT statement to have a similar number of fields in result sets of similar data types.

Syntax: 

SELECT expression_1, expression_2, ... expression_n
FROM tables
[WHERE condition(s)]
UNION ALL
SELECT expression_1, expression_2, ... expression_n
FROM tables
[WHERE condition(s)];

Here is an explanation for the above parameters:

The expression_1, expression_2, ... expression_n are the calculations or columns that you need to retrieve.

The tables are the tables from which you need to retrieve records.

The WHERE condition(s) are the conditions that must be met for records to be retrieved.

Note: Both expressions must have an equal number of expressions.

We will use the following tables:

Book:

Price:

Run the following command: 

 
SELECT id
FROM Book
UNION ALL
SELECT id
FROM price;

It should return the following:

The duplicates have not been removed.

ORDER BY

The PostgreSQL UNION operator can be used together with the ORDER BY clause to order the query results. To demonstrate this, we will use the following tables:

Price:

Price2:

Here is the command that demonstrates how to use the UNION operator together with the ORDER BY clause: 

SELECT *
FROM Price
UNION
SELECT *
FROM Price2
ORDER BY price;

The command will return the following:

The records were ordered by the price column. The clause orders the records in ascending order by default. To order them in descending order, add the DESC clause as shown below: 

SELECT *
FROM Price
UNION
SELECT *
FROM Price2
ORDER BY price DESC;

The command will return the following:

The records have been ordered based on the price column in descending order.

When to use Union and When to use Union all?

Use the UNION operator when you have multiple tables with a similar structure but split for a reason. It is good when you need to remove/eliminate duplicate records.

Use the UNION ALL operator when you don't need to remove/eliminate duplicate records.

Using pgAdmin

Now let's see how all three actions performed using pgAdmin.

Union

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

  1. From the navigation bar on the left- Click Databases.
  2. Click Demo.

Step 3) Type the query in the query editor: 

SELECT *
FROM Price
UNION
SELECT *
FROM Price2
ORDER BY price DESC;

Step 4) Click the Execute button.

It should return the following:

Union All

Step 1) Login to your pgAdmin account.

Step 2)

  1. From the navigation bar on the left, click Databases.
  2. Click Demo.

Step 3) Type the query in the query editor: 

SELECT id
FROM Book
UNION ALL
SELECT id
FROM price;

Step 4) Click the Execute button.

It should return the following:

ORDER BY

The UNION ALL operator can be combined with the ORDER BY clause to order results in the result set. For example: 

SELECT id
FROM Book
UNION ALL
SELECT id
FROM price
ORDER BY id;

The command will return the following:

The results have been ordered.

Summary:

  • The PostgreSQL UNION operator combines results from more than one SELECT statement into one result set.
  • The UNION operator doesn't return duplicate records.
  • To order the results, combine it with the ORDER BY clause.
  • The UNION ALL operator combines results from more than one SELECT statement into one result set.
  • The UNION ALL operator does not remove duplicates.

Download the Database used in this Tutorial

 

YOU MIGHT LIKE: