UNION and UNION ALL in PostgreSQL

Introduction

UNION and UNION ALL combine results from two or more queries into one list. They help you see data from different places together.

You want to list all customers from two different regions in one list.

You need to combine product names from two tables to see everything available.

You want to merge sales records from two months into a single report.

You want to get all unique email addresses from two contact lists.

You want to include duplicates when combining two lists of orders.

Syntax

PostgreSQL

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

UNION removes duplicate rows between the queries.

UNION ALL keeps all rows, including duplicates.

Examples

Combines customer names from two regions, removing duplicates.

PostgreSQL

SELECT name FROM customers_north
UNION
SELECT name FROM customers_south;

Combines customer names from two regions, keeping duplicates.

PostgreSQL

SELECT name FROM customers_north
UNION ALL
SELECT name FROM customers_south;

Lists all unique products from online and store sales.

PostgreSQL

SELECT product FROM products_online
UNION
SELECT product FROM products_store;

Sample Program

This example creates two tables with fruit names. The first query combines them with UNION, showing each fruit once. The second query uses UNION ALL, showing all fruits including duplicates.

PostgreSQL

CREATE TABLE fruits1 (name TEXT);
CREATE TABLE fruits2 (name TEXT);

INSERT INTO fruits1 VALUES ('Apple'), ('Banana'), ('Cherry');
INSERT INTO fruits2 VALUES ('Banana'), ('Date'), ('Apple');

-- Using UNION (removes duplicates)
SELECT name FROM fruits1
UNION
SELECT name FROM fruits2;

-- Using UNION ALL (keeps duplicates)
SELECT name FROM fruits1
UNION ALL
SELECT name FROM fruits2;

Important Notes

All SELECT queries combined by UNION or UNION ALL must have the same number of columns and compatible data types.

UNION removes duplicates and sorts the result, so it can be slower than UNION ALL.

Use UNION ALL when you want to keep duplicates and improve performance.

Summary

UNION combines query results and removes duplicates.

UNION ALL combines query results and keeps duplicates.

Both require the same number of columns with matching types.