PostgreSQL Union

In this section, we are going to understand the working of the PostgreSQL UNION operator, which is used to combine the output of various commands into a single output.

And the examples of the PostgreSQL union command with PostgreSQL union all using the order by clause.

What is the PostgreSQL UNION operator?

In PostgreSQL, the UNION operator combines one or more SELECT commands into a single output.

Syntax of PostgreSQL Union operator

The syntax of the PostgreSQL union operator is as follows, which help us to associate the output from two commands:

SELECT select_list_1
FROM table_expresssion_1
UNION
SELECT select_list_2
FROM table_expression_2

If we want to combine the output of two commands with the help of the UNION operator, then the commands must follow the below instructions:

The specified data type must be compatible.
The order of the columns and the numbers in the select list of both commands must be similar.

Note: The UNION operator is also used to delete all matching rows from the combined data set. And we can use the UNION ALL in place of UNION to recollect the duplicate rows.

In the below image, we can understand the working of the UNION Operator:

PostgreSQL UNION with ORDER BY clause

As we learned above, the UNION operator could place the rows from the output of the initial command before, after, or between the rows from the additional command's output.

We can use the ORDER BY clause in the second command to sort rows in the final output.

The UNION operator is used regularly to combine data from the same tables that are not effortlessly normalized.

To understand the working of PostgreSQL Union operator, firstly, we will set up sample tables with the help of the below commands:

In the below commands, we will create two tables as top_rated_cars and most_reliable_cars with the CREATE command's help and inserting some values using the INSERT command.

To create the top_rated_cars into an Organization database, we use the CREATE command.

But, before creating the top_rated_cars table, we will use the DROP TABLE command if a similar table is already existing in the Organization database.

Output

After executing the above command, we will get the following window message: The top_rated_cars table does not exist.

The top_rated_cars table contains various columns such as Car_name, launch_year column.

CREATE TABLE top_rated_cars(
	Car_name VARCHAR NOT NULL,
	launch_year SMALLINT
);

Output

On executing the above command, we will get the following message: The top_rated_cars table has been created successfully into the Organization database.

After creating the top_rated_cars table successfully, we will enter some values into it with the INSERT command's help.

INSERT INTO 
  top_rated_cars(Car_name,launch_year)
VALUES
   ('Chevrolet Silveradon',2020),
   ('Nissan Rogue',2020),
   ('Mercedes-Benz GLB',2019);

Output

After implementing the above command, we will get the following message window: the three values have been inserted successfully into the top_rated_cars table.

To create the most_reliable_cars into an Organization database, we use the CREATE command.

But, before creating the most_reliable_cars table, we will use the DROP TABLE command if a similar table is already existing in the Organization database.

Output

After executing the above command, we will get the following window message: The most_reliable_cars table does not exist.

The table most_reliable_cars contains various columns such as Car_name, launch_year column.

CREATE TABLE most_reliable_cars (
	Car_name VARCHAR NOT NULL,
	launch_year SMALLINT
);

Output

On executing the above command, we will get the following message, which displays that the most_reliable_cars table has been created successfully into the Organization database.

After creating the most_reliable_cars table successfully, we will enter some values into it with the INSERT command's help.

INSERT INTO 
  most_reliable_cars(Car_name,launch_year)
VALUES
   ('Toyota Prius Prime',2020),
   ('Nissan Rogue',2020),
   ('Kia Forte',2019);

Output

After implementing the above command, we will get the following message window, which displays that the three values have been inserted successfully into the most_reliable_cars table.

We are using the SELECT command in the below command if we want to see the data from a top_rated_cars table.

Output

After executing the above command, we will get the below result:

The following statement returns the data from the most_reliable_cars table:

Output

On implementing the above command, we will get the below output:

Example of PostgreSQL UNION

Let us see a sample example to understand the working of the PostgreSQL Union command.

Example of Simple PostgreSQL UNION

The UNION operator is used to eliminates the matching rows.

In the following example, we use the UNION operator to merge the data from both tables (top_rated_cars and the most_reliable_cars).

SELECT * FROM top_rated_cars
UNION
SELECT * FROM most_reliable_cars;

Output

After executing the above command, we will get the below output, which contains the five rows in the output, since the UNION operator eliminates one matching row.

PostgreSQL UNION ALL

The UNION ALL operator unites the output from multiple SELECT commands without eliminating the duplicates rows.

The UNION ALL operator involves each SELECT command to have the equivalent number of fields in the output of the same data types.

Syntax of PostgreSQL UNION ALL

The Syntax of PostgreSQL UNION ALL is as follows:

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)];

We have the following parameters, which are used in the above illustration:

Parameters	Explanation
expression_1, expression_2, ... expression_n	These are the calculations or columns, which we want to retrieve.
Tables	The table parameter is used to retrieve records from the various table.
Where condition	These are the conditions, which must be met for values to be saved.

Note: In the above syntax, both the expressions must have an equivalent number of expressions.

Example of PostgreSQL UNION ALL

In the below example, we will use the UNION ALL operator to combine the outputs from both the top_rated_cars and most_reliable_cars tables.

SELECT * FROM top_rated_cars
UNION ALL
SELECT * FROM most_reliable_cars;

Output

After executing the above command, we will get the following output, which retained the matching rows.

PostgreSQL UNION ALL with ORDER BY clause

The PostgreSQL UNION operator can be used along with the ORDER BY clause to order the command outputs.

Example of PostgreSQL UNION ALL with ORDER BY clause

In the below command, we are using the PostgreSQL UNION ALL with ORDER clause to sort the output, which is returned by the UNION operator.

And we use the ORDER BY clause at the end of the last command as shown in the below example:

SELECT * FROM top_rated_cars
UNION ALL
SELECT * FROM most_reliable_cars
ORDER BY Car_name;

Output

After executing the above command, we will get the following result, displaying all the data from both the tables.

Note: If we use the ORDER BY clause at the end of each command, the combined output will not be sorted as we expected.

Since when the UNION operator merges the sorted result sets from all the commands, it does not guarantee rows' order in the final output.

Overview

In the PostgreSQL UNION section, we have learned the following topics:

We have used the PostgreSQL UNION and UNION ALLto merge the outputs from various commands into a single output.
We have also used the PostgreSQL UNION ALL with ORDER By clause to order the command outputs.

Next TopicPostgreSQL Intersect

← prev next →