Why set operations are needed in SQL

Introduction

Set operations help combine or compare data from two or more tables easily. They let you find common, different, or all data without writing complex code.

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 employees who work in department X but not in department Y.

You want to merge two lists of emails without duplicates.

Syntax

SQL

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 all rows, INTERSECT finds common rows, EXCEPT finds rows in first but not in second.

Examples

Finds all unique customers who bought from store A or B.

SQL

SELECT name FROM customers_in_storeA
UNION
SELECT name FROM customers_in_storeB;

Finds products sold both online and in physical stores.

SQL

SELECT product_id FROM online_products
INTERSECT
SELECT product_id FROM physical_store_products;

Finds employees in department X but not in department Y.

SQL

SELECT employee_id FROM departmentX
EXCEPT
SELECT employee_id FROM departmentY;

Sample Program

This example creates two tables with customer names from two stores. The UNION query lists all unique customers who bought from either store.

SQL

CREATE TABLE storeA_customers (name VARCHAR(20));
CREATE TABLE storeB_customers (name VARCHAR(20));

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;

Important Notes

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

UNION removes duplicates, so it can be slower than UNION ALL.

Not all databases support INTERSECT and EXCEPT; check your system.

Summary

Set operations combine or compare data from multiple tables simply.

They help find common, different, or all unique rows.

Use UNION, INTERSECT, and EXCEPT to answer real-world questions about data overlap and difference.