Introduction to Set Operations
You'll learn how SQL can combine results from multiple separate queries using mathematical set theory.
What Are Set Operations?
In SQL, set operations let you stack, merge, or compare the results of two or more independent SELECT queries. Instead of joining tables side-by-side (adding columns), set operations work vertically—combining rows from different result sets.
Think of it like stacking physical reports: If you have a list of customers from Region A and another list from Region B, a set operation can combine them into one unified list, remove duplicates, or even show you which customers appear in both.
The Core Idea
Set operations treat each query's result as a mathematical set—a collection of unique items. SQL provides operators that mirror set theory:
- Combine all rows from both queries
- Find only common rows between queries
- Identify rows unique to one query
The key requirement: both queries must return the same number of columns with compatible data types. You're essentially aligning result sets row-by-row, so the structure must match.
Why Use Set Operations?
They're powerful for:
- Merging data from similar tables (e.g., combining sales from different years)
- Finding overlaps or differences between datasets
- Simplifying complex multi-source queries without joins
Unlike joins (which match rows based on relationships), set operations treat each query independently, then apply mathematical logic to their results.
Key Takeaway: Set operations combine results from multiple queries vertically using set theory principles, requiring matching column structures and enabling powerful data comparisons and merges.