Why UNION and UNION ALL in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could combine endless lists instantly without missing or repeating anyone?

The Scenario

Imagine you have two separate lists of customer names from two different stores, and you want to see all customers combined in one list.

You try to write down each list on paper and then manually check for duplicates or just combine them.

The Problem

This manual way is slow and tiring. You might miss some names or count some twice by accident.

It's hard to keep track and update the list when new customers come in.

The Solution

Using UNION and UNION ALL in SQL lets you combine these lists quickly and correctly.

UNION removes duplicates automatically, while UNION ALL keeps all entries, even if repeated.

Before vs After

✗ Before

List1 = ['Alice', 'Bob']
List2 = ['Bob', 'Charlie']
Combined = []
for name in List1:
  if name not in Combined:
    Combined.append(name)
for name in List2:
  if name not in Combined:
    Combined.append(name)

✓ After

SELECT name FROM store1_customers
UNION
SELECT name FROM store2_customers;

What It Enables

It makes combining data from multiple sources fast, reliable, and easy to update.

Real Life Example

A company merges customer lists from two branches to send a single newsletter without sending duplicates.

Key Takeaways

Manually combining lists is slow and error-prone.

UNION removes duplicates when merging data.

UNION ALL keeps all data including duplicates.