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:
- What is PostgreSQL Union?
- Syntax
- Union
- Union All
- ORDER BY
- When to use Union and When to use Union all?
- Using pgAdmin
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)
- From the navigation bar on the left- Click Databases.
- 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)
- From the navigation bar on the left, click Databases.
- 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
