SQL: Set Operations

Last updated: 2026-10-09

1. 🌍 Real-World Analogy

Imagine you have two stacks of business cards:

• UNION — Combine the two stacks, keeping only one copy of duplicates
• UNION ALL — Combine all cards from both stacks, keeping duplicates
• INTERSECT — Pick out only those who appear in both stacks
• EXCEPT — Remove from the first stack anyone who also appears in the second

Set operations perform "merge, intersect, and difference" operations on two query result sets.

2. 🎯 Core Concepts

(1) UNION — Deduplicated Merge

Merges two result sets, automatically removing duplicate rows.

SELECT city FROM employees
UNION
SELECT city FROM departments;

(2) UNION ALL — Non-Deduplicated Merge

Merges two result sets, retaining all duplicate rows. Better performance because no deduplication is needed.

SELECT city FROM employees
UNION ALL
SELECT city FROM departments;

(3) INTERSECT — Intersection

Returns rows that exist in both result sets.

SELECT city FROM employees
INTERSECT
SELECT city FROM departments;

(4) EXCEPT / MINUS — Difference

Returns rows that exist in the first result set but do not exist in the second.

-- EXCEPT (SQL Server, PostgreSQL)
SELECT city FROM employees
EXCEPT
SELECT city FROM departments;

-- MINUS (Oracle)
SELECT city FROM employees
MINUS
SELECT city FROM departments;

(5) Column Matching Rules

When using set operations, the two SELECT statements must satisfy:

-- Correct: two columns, types match
SELECT first_name, salary FROM employees
UNION
SELECT department_name, budget FROM departments;

-- Wrong: different column count
SELECT first_name, salary FROM employees
UNION
SELECT department_name;  -- ❌ Column count mismatch

(6) 💡 When to Use Which Operation

3. 📝 Basic Syntax

-- UNION syntax
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2
[ORDER BY column1];

-- UNION ALL syntax
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

-- INTERSECT syntax
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

-- EXCEPT syntax
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

4. 📌 Examples

▶ Example: Query All Name Sources

Merge employee names and department names into one result set.

-- View all "name" sources: employees + department managers
SELECT first_name AS name, 'Employee' AS source
FROM employees
UNION ALL
SELECT department_name, 'Department'
FROM departments
ORDER BY source;

Explanation: A constant column is added to mark the data source for easy distinction.

▶ Example: Find Cities With Employees But No Departments

-- Cities with employees minus cities with departments
SELECT city FROM employees
EXCEPT
SELECT city FROM departments;

Approach: Use EXCEPT as a difference operation to quickly find "cities that only appear in employees."

5. 🎬 Scenario Walkthroughs

(1) Scenario 1: Merge Multi-Channel Customer Lists

The company has online and offline customer tables that need to be merged and deduplicated to create a complete customer directory.

-- Online customers
SELECT customer_name, email, 'Online' AS channel
FROM online_customers
UNION
-- Offline customers
SELECT customer_name, email, 'Offline'
FROM offline_customers
ORDER BY customer_name;

Key point: Use UNION instead of UNION ALL to automatically remove duplicate customers.

(2) Scenario 2: Compare Sales Differences Between Two Months

Find products that had sales this month but not last month.

-- Products sold this month
SELECT product_id FROM orders
WHERE order_date >= '2026-06-01'
EXCEPT
-- Products sold last month
SELECT product_id FROM orders
WHERE order_date >= '2026-05-01'
  AND order_date < '2026-06-01';

Key point: EXCEPT is naturally suited for "finding differences" scenarios.

❓ FAQ

📖 Summary

• Column count and types must match
• ORDER BY can only be placed at the end of the statement
• Prefer UNION ALL when you're sure there are no duplicates for better performance

📝 Exercises

1. Use UNION to merge employees with salary above 8000 from the employees table and department names with budget above 100000 from the departments table.
2. Use INTERSECT to find cities that exist in both the employees and departments tables.
3. Use EXCEPT to find cities that have departments but no employees assigned.
4. Think about it: What happens if the column types of two query results don't fully match (e.g., INT and VARCHAR) in a set operation?

6. Next Lesson

Next we'll learn Constraints and Keys — understand how to use PRIMARY KEY, FOREIGN KEY, and other constraints to ensure data integrity.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us