PostgreSQL Union, Union ALL with Examples

By : Juniper Willow Juniper Willow
Hours Updated

⚡ Smart Summary

PostgreSQL UNION combines the results of two or more SELECT statements into a single result set and removes duplicate rows, while UNION ALL keeps every row, giving a faster option when duplicates do not matter.

  • 🔗 Combine: UNION merges rows from multiple SELECT statements into one result set.
  • 🧹 Duplicates: UNION removes duplicate rows, while UNION ALL keeps every matching row.
  • 📐 Rules: Each SELECT must return the same number of columns with compatible data types.
  • ↕️ ORDER BY: A single ORDER BY at the end sorts the combined result set.
  • ⚡ Performance: UNION ALL runs faster because it skips the duplicate-removal step.
  • 🤖 AI Help: AI assistants align columns and choose between UNION and UNION ALL.
Read More

PostgreSQL UNION and UNION ALL

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 the same number of columns.
  • 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.

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: Since the UNION operator does not return duplicates, the use of UNION DISTINCT will have no impact on the results.

PostgreSQL Union

The UNION operator removes duplicates, in the same way that SELECT DISTINCT does. Let us demonstrate this. We have a database named Demo with the following tables:

Book:

PostgreSQL Union

Price:

PostgreSQL Union

Let us run the following command:

SELECT id
FROM Book
UNION
SELECT id
FROM Price;

The command will return the following:

PostgreSQL Union

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 does not return duplicates.

RELATED ARTICLES

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:

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 SELECT statements must have an equal number of expressions.

We will use the following tables:

Book:

PostgreSQL Union All

Price:

PostgreSQL Union All

Run the following command:

SELECT id
FROM Book
UNION ALL
SELECT id
FROM price;

It should return the following:

PostgreSQL Union All

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:

ORDER BY

Price2:

ORDER BY

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:

ORDER BY

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:

ORDER BY

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 or eliminate duplicate records.

Use the UNION ALL operator when you do not need to remove or eliminate duplicate records.

Using pgAdmin

Now let us see how all three actions are performed using pgAdmin.

How to Use PostgreSQL Union Using pgAdmin

Following is a step-by-step process on how to use PostgreSQL UNION through pgAdmin:

Step 1) Login to your pgAdmin account.

Step 2) From the navigation bar on the left, click Databases, then click Demo.

Use PostgreSQL Union Using pgAdmin

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.

Use PostgreSQL Union Using pgAdmin

It should return the following:

Use PostgreSQL Union Using pgAdmin

Union All

Step 1) Login to your pgAdmin account.

Step 2) From the navigation bar on the left, click Databases, then click Demo.

Use PostgreSQL Union All Using pgAdmin

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.

Use PostgreSQL Union All Using pgAdmin

It should return the following:

Use PostgreSQL Union All Using pgAdmin

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:

Use PostgreSQL ORDER BY Using pgAdmin

The results have been ordered.

Download the Database used in this Tutorial

FAQs

UNION ALL is faster because it simply concatenates rows. UNION must sort or hash the combined rows to remove duplicates, which adds overhead. When duplicates do not matter, UNION ALL is the more efficient choice.

Yes. Every SELECT in a UNION must return the same number of columns, and matching columns must have compatible data types in the same order. Otherwise PostgreSQL raises an error and the query fails.

Yes. The column names can differ because UNION matches columns by position, not by name. The data types must still be compatible, and the result set uses the column names from the first SELECT statement.

UNION stacks the rows of two result sets vertically into one column list, removing duplicates. A JOIN combines columns from different tables horizontally based on a related key, so it widens rows instead of appending them.

ORDER BY sorts the final combined result, so PostgreSQL allows just one ORDER BY placed after the last SELECT. Sorting the individual queries first would be meaningless once their rows are merged together.

AI assistants like GitHub Copilot align the column lists of each SELECT, flag mismatched data types, and recommend UNION or UNION ALL based on whether duplicates should be kept, reducing common query errors.

Yes. By examining your tables and goal, an AI assistant can tell whether duplicate rows are possible and meaningful, then suggest UNION ALL when duplicates are acceptable to gain speed, or UNION when they must be removed.

UNION returns rows found in either query. INTERSECT returns only rows present in both queries, and EXCEPT returns rows in the first query but not the second. All three remove duplicates by default.

Summarize this post with: