SQL
Database Design Tutorial: Learn Data Modeling
What is Database Design? Database Design is a collection of processes that facilitate the...
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 UNION operator is normally used to combine data from related tables that have not been normalized perfectly.
In this PostgreSQL tutorial, you will learn:
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.
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.
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.
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.
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.
Now let's see how all three actions performed using pgAdmin.
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
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:
Step 1) Login to your pgAdmin account.
Step 2)
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:
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.
Download the Database used in this Tutorial
What is Database Design? Database Design is a collection of processes that facilitate the...
What is For Loop? "FOR LOOP" statement is best suitable when you want to execute a code for a...
What is WHERE Clause in MySQL? WHERE Clause in MySQL is a keyword used to specify the exact...
SQL is the standard language for dealing with Relational Databases. SQL can be used to insert,...
SQL stands for Structured Query Language is a domain specific programming language for managing...
Aggregate Functions are all about Performing calculations on multiple rows Of a single column of a...