PostgreSQL UNION

Combine results from multiple SELECT queries into one result set

🔗 What is UNION?

UNION combines results from two or more SELECT statements into a single result set. It automatically removes duplicate rows unless you use UNION ALL. 


-- Basic UNION syntax
SELECT columns FROM table1
UNION
SELECT columns FROM table2;

Key UNION Concepts

🔄

Combine Results

Merges multiple query results vertically 

SELECT name FROM customers
UNION
SELECT name FROM suppliers;
🚫

Remove Duplicates

UNION removes duplicate rows automatically 

-- Duplicates are removed
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
📋

UNION ALL

Keeps all rows including duplicates 

-- Keeps duplicates
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
⚖️

Same Columns

Queries must have matching column count 

-- Both SELECT must have same number of columns
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

🔹 Basic UNION Example

Combine customer and supplier names into one list. UNION removes duplicates, so if a name appears in both tables, it shows only once in the result. 

-- Sample tables
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE suppliers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

-- UNION query
SELECT name, city FROM customers
UNION
SELECT name, city FROM suppliers
ORDER BY name;

Output:

name city
ABC Corp New York
John Doe Boston
Tech Supply Seattle

🔹 UNION vs UNION ALL

UNION removes duplicates while UNION ALL keeps everything. Use UNION ALL when you know there are no duplicates or when you need all rows for better performance. 

-- UNION removes duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL keeps all rows (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Output Comparison:

UNION (3 rows): Boston, New York, Seattle

UNION ALL (5 rows): Boston, New York, New York, Seattle, Boston

🔹 UNION with Different Tables

Combine data from tables with different structures by selecting matching columns. Add literal values to identify the source table in the combined results. 

-- Combine with source identifier
SELECT 
    name, 
    email, 
    'Customer' AS type
FROM customers
UNION
SELECT 
    name, 
    email, 
    'Supplier' AS type
FROM suppliers
ORDER BY type, name;

Output:

name email type
John Doe [email protected] Customer
ABC Corp [email protected] Supplier

🔹 Multiple UNION Operations

Chain multiple UNION operations to combine three or more queries. Each query must have the same number and compatible types of columns in the same order. 

-- Combine three tables
SELECT product_name, price FROM electronics
UNION
SELECT product_name, price FROM furniture
UNION
SELECT product_name, price FROM clothing
ORDER BY price DESC;

Output:

product_name price
Laptop 999.99
Sofa 599.99
Jacket 89.99

🔹 UNION with WHERE and ORDER BY

Filter individual queries with WHERE clauses before combining. Apply ORDER BY at the end to sort the final combined result set from all queries. 

-- Filter each query, then combine and sort
SELECT name, city FROM customers
WHERE city = 'Boston'
UNION
SELECT name, city FROM suppliers
WHERE city = 'Seattle'
ORDER BY name;

Output:

name city
John Doe Boston
Tech Supply Seattle

🧠 Test Your Knowledge

What is the difference between UNION and UNION ALL?