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 PostgreSQL Union PostgreSQL Union All ORDER BY When to use Union and When to use Union all? Using pgAdmin
Syntax
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.
PostgreSQL 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: 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.
PostgreSQL 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: 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: 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: 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: 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.
How to Use PostgreSQL Union Using pgAdmin
Following is Step by Step Process on How to Use PostgreSQL Union Using pgAdmin To accomplish the same through pgAdmin, do this: Step 1) Login Login to your pgAdmin account. Step 2) Click on Databases
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query Type the query in the query editor: Step 4) Click the Execute button. Next, Click the Execute button. As show in below image.
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: 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: 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
