---
description: PostgreSQL tutorial explains how to use the PostgreSQL UNION and UNION ALL Operator to Remove Duplicates with Syntax and Examples.
title: PostgreSQL Union, Union ALL with Examples
image: https://www.guru99.com/images/postgresql-union-union-all.png
---

 

[Skip to content](#main) 

**⚡ 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 ](javascript:void%280%29;) 

![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](https://www.guru99.com/postgresql-select-distinct.html) does. Let us demonstrate this. We have a database named Demo with the following tables:

**Book:**

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU1.png)

**Price:**

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU2.png)

Let us run the following command:

SELECT id
FROM Book
UNION
SELECT id
FROM Price;

The command will return the following:

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU3.png)

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](https://www.guru99.com/postgresql-tutorial.html) UNION operator does not return duplicates.

### RELATED ARTICLES

* [PostgreSQL vs MySQL – Difference Between Them ](https://www.guru99.com/postgresql-vs-mysql-difference.html "PostgreSQL vs MySQL – Difference Between Them")
* [PostgreSQL ALTER TABLE: Add & Rename Column ](https://www.guru99.com/postgresql-alter-add-rename-column-table.html "PostgreSQL ALTER TABLE: Add & Rename Column")
* [PostgreSQL DELETE Rows from Select ](https://www.guru99.com/postgresql-delete-row.html "PostgreSQL DELETE Rows from Select")
* [PostgreSQL EXISTS with SELECT Operator (Example) ](https://www.guru99.com/postgresql-exists.html "PostgreSQL EXISTS with SELECT Operator (Example)")

## 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:**

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU4.png)

**Price:**

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU5.png)

Run the following command:

SELECT id
FROM Book
UNION ALL
SELECT id
FROM price;

It should return the following:

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU6.png)

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:**

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU7.png)

**Price2:**

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU8.png)

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:

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU9.png)

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:

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU10.png)

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.

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU11.png)

**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.

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU12.png)

It should return the following:

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU13.png)

### Union All

**Step 1)** Login to your pgAdmin account.

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

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU14.png)

**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.

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU15.png)

It should return the following:

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU16.png)

### 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:

[](https://www.guru99.com/images/1/102219%5F1037%5FPostgreSQLU17.png)

The results have been ordered.

[ Download the Database used in this Tutorial](https://drive.google.com/uc?export=download&id=1BqQSvYTmNz4hvZok7iYG9ha%5Fo48%5FVF2b)

## FAQs

⚡ Is UNION or UNION ALL faster in PostgreSQL?

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.

🔢 Do the SELECT statements in a UNION need the same number of columns?

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.

🏷️ Can I use UNION on tables with different column names?

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.

➕ What is the difference between UNION and JOIN?

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.

↕️ Why can ORDER BY only appear at the end of a UNION query?

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.

🤖 How can AI help write PostgreSQL UNION queries?

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.

🧠 Can an AI assistant decide when UNION ALL is safe to use?

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.

🔵 How is UNION different from INTERSECT and EXCEPT?

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:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/postgresql-union-union-all.png","url":"https://www.guru99.com/images/postgresql-union-union-all.png","width":"700","height":"250","caption":"PostgreSQL Union, Union ALL","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/postgresql-union-example.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/postgresql","name":"PostgreSQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/postgresql-union-example.html","name":"PostgreSQL Union, Union ALL with Examples"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/postgresql-union-example.html#webpage","url":"https://www.guru99.com/postgresql-union-example.html","name":"PostgreSQL Union, Union ALL with Examples","dateModified":"2026-07-02T12:20:09+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/postgresql-union-union-all.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/postgresql-union-example.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow","description":"I am Juniper Willow, a PostgreSQL Developer, offering expert guidance to help you optimize and master PostgreSQL database development.","url":"https://www.guru99.com/author/juniper","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/juniper-willow-author.png","url":"https://www.guru99.com/images/juniper-willow-author.png","caption":"Juniper Willow","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"PostgreSQL","headline":"PostgreSQL Union, Union ALL with Examples","description":"PostgreSQL tutorial explains how to use the PostgreSQL UNION and UNION ALL Operator to Remove Duplicates with Syntax and Examples.","keywords":"postgresql, sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow"},"dateModified":"2026-07-02T12:20:09+05:30","image":{"@id":"https://www.guru99.com/images/postgresql-union-union-all.png"},"copyrightYear":"2026","name":"PostgreSQL Union, Union ALL with Examples","subjectOf":[{"@type":"HowTo","name":"How to Use PostgreSQL Union Using pgAdmin","description":"Following is Step by Step Process on How to Use PostgreSQL Union Using pgAdmin","step":[{"@type":"HowToStep","name":"Step 1) Login","text":"Login to your pgAdmin account.","url":"https://www.guru99.com/postgresql-union-example.html#step1"},{"@type":"HowToStep","name":"Step 2) Click on Databases","text":"From the navigation bar on the left- Click Databases.","image":"https://cdn.guru99.com/images/1/102219_1037_PostgreSQLU11.png","url":"https://www.guru99.com/postgresql-union-example.html#step2"},{"@type":"HowToStep","name":"Step 3) Type the query","text":"Type the below query in the query editor:","url":"https://www.guru99.com/postgresql-union-example.html#step3"},{"@type":"HowToStep","name":"Step 4) Click the Execute button.","text":"Next, Click the Execute button. It should return the following:","image":"https://cdn.guru99.com/images/1/102219_1037_PostgreSQLU12.png","url":"https://www.guru99.com/postgresql-union-example.html#step4"}]},{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Is UNION or UNION ALL faster in PostgreSQL?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Do the SELECT statements in a UNION need the same number of columns?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can I use UNION on tables with different column names?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"What is the difference between UNION and JOIN?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Why can ORDER BY only appear at the end of a UNION query?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How can AI help write PostgreSQL UNION queries?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can an AI assistant decide when UNION ALL is safe to use?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How is UNION different from INTERSECT and EXCEPT?","acceptedAnswer":{"@type":"Answer","text":"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."}}]}],"@id":"https://www.guru99.com/postgresql-union-example.html#schema-60381","isPartOf":{"@id":"https://www.guru99.com/postgresql-union-example.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/postgresql-union-example.html#webpage"}}]}
```