SET Operators (UNION, INTERSECT, EXCEPT)
What Are Set Operators in SQL?
Set operators in SQL are used to combine the results of two or more SELECT queries. They work like how Venn diagrams operate in mathematics—helping us identify common, distinct, or exclusive values between datasets.
These operators are particularly useful when comparing or merging data from different tables or queries that return the same kind of data (i.e., same number of columns and compatible data types).
Main Set Operators in SQL
1. UNION
- Purpose: Combines results from two or more queries and removes duplicates by default.
- Use Case: When you want to merge lists from two departments (like marketing and sales contacts) and don’t want duplicate names.
2. UNION ALL
- Purpose: Similar to UNION, but keeps all records, including duplicates.
- Use Case: When you need a complete log of transactions from two sources, even if some entries are repeated.
3. INTERSECT
- Purpose: Returns only the records that are common to both queries.
- Use Case: Useful to find customers who have made purchases in both online and offline channels.
4. EXCEPT (also called MINUS in some databases)
- Purpose: Returns records from the first query that are not found in the second.
- Use Case: Helps identify customers who have signed up but never made a purchase.
Real-Life Scenarios
| Use Case | Operator | Description |
|---|---|---|
| Merging customer lists from two different cities | UNION | Combines both lists, removes duplicates |
| Listing all job applicants from two recruitment sources | UNION ALL | Keeps all, including repeated entries |
| Identifying users active in both the mobile app and website | INTERSECT | Shows only common users |
| Filtering out employees who left last year from current employee list | EXCEPT | Shows only current, excluding former ones |
Rules for Using Set Operators
- The number of columns in each query must match.
- The data types of columns must be compatible (e.g., numbers with numbers, dates with dates).
- You can use ORDER BY only once at the end of the full query, not in individual queries.
Why Teach Set Operators?
- Encourages critical thinking by comparing data sets.
- Reinforces data analysis skills without complex joins.
- Useful in data cleaning, reporting, and integration tasks.
Key Takeaways for Students
- Set operators let you perform logic-based operations on query results.
- UNION = combine and remove duplicates.
- UNION ALL = combine and keep duplicates.
- INTERSECT = find common results.
- EXCEPT = subtract one result from another.
- Ideal for data comparison, deduplication, and merging.
