Why set operations matter in PostgreSQL

Introduction

Set operations help combine or compare data from different tables easily. They let you find common, different, or all unique data quickly.

You want to find all customers who bought from either store A or store B.

You need to see which products are sold in both online and physical stores.

You want to list all unique email addresses from two mailing lists.

You want to find customers who bought from store A but not from store B.

Syntax

PostgreSQL

SELECT column_list FROM table1
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT column_list FROM table2;

All SELECT statements must have the same number of columns and compatible data types.

UNION removes duplicates, UNION ALL keeps duplicates.

Examples

Finds all unique customer names from both stores.

PostgreSQL

SELECT name FROM customers_storeA
UNION
SELECT name FROM customers_storeB;

Finds products sold both online and in stores.

PostgreSQL

SELECT product_id FROM online_sales
INTERSECT
SELECT product_id FROM store_sales;

Finds emails in list1 but not in list2.

PostgreSQL

SELECT email FROM mailing_list1
EXCEPT
SELECT email FROM mailing_list2;

Sample Program

This query lists all unique customers from both stores, sorted by name.

PostgreSQL

CREATE TABLE storeA_customers (name TEXT);
CREATE TABLE storeB_customers (name TEXT);

INSERT INTO storeA_customers VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO storeB_customers VALUES ('Bob'), ('Diana');

SELECT name FROM storeA_customers
UNION
SELECT name FROM storeB_customers
ORDER BY name;

Important Notes

Set operations work best when columns match in type and order.

UNION removes duplicates, but UNION ALL is faster if duplicates are okay.

Use INTERSECT to find common data, EXCEPT to find differences.

Summary

Set operations combine or compare data from multiple queries.

They help find unique, common, or different data easily.

Remember to match columns and data types in all SELECTs.