Introduction
In SQL, set operations like UNION and INTERSECT allow you to combine results from multiple queries into a single dataset. These operations are crucial for reporting, analytics, and complex data integration tasks.
Why It Matters
- Combine data from multiple sources or queries.
- Perform comparisons and find common or distinct records.
- Useful for analytics, deduplication, and merging datasets.
Real-world analogy:
Think of UNION as merging two guest lists into one combined list. INTERSECT is like finding people who appear on both lists.
Core Concepts
UNION
Combines results of two or more SELECT queries, removing duplicates by default.
UNION ALL
Same as UNION but includes duplicates for performance.
INTERSECT
Returns only rows that appear in both queries.
Rules for Set Operations
- Queries must have the same number of columns.
- Data types must be compatible.
SQL Examples
Sample Tables
Customers_US:
| customer_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
Customers_EU:
| customer_id | name |
|---|---|
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
UNION Example
SELECT name FROM customers_us
UNION
SELECT name FROM customers_eu;
Result:
| name |
|---|
| Alice |
| Bob |
| Charlie |
| David |
UNION ALL Example
SELECT name FROM customers_us
UNION ALL
SELECT name FROM customers_eu;
Result:
| name |
|---|
| Alice |
| Bob |
| Charlie |
| Bob |
| Charlie |
| David |
INTERSECT Example (PostgreSQL/Oracle)
SELECT name FROM customers_us
INTERSECT
SELECT name FROM customers_eu;
Result:
| name |
|---|
| Bob |
| Charlie |
Real-World Use Cases
- Merging user data: Combine datasets from different regions.
- Deduplication: UNION removes duplicates across queries.
- Finding common customers: INTERSECT identifies overlapping records.
Common Mistakes and Anti-Patterns
- Mismatched columns: Causes errors in UNION/INTERSECT.
- Using UNION instead of UNION ALL unnecessarily: Adds overhead.
- Forgetting indexes: Slows down large set operations.
Performance and Scalability Implications
- UNION requires sorting to remove duplicates; UNION ALL is faster.
- INTERSECT can be expensive on large datasets; indexes help.
- For huge data, consider temporary tables or materialized views.
RDBMS Comparison
| Feature | PostgreSQL | MySQL | Oracle |
|---|---|---|---|
| UNION | Supported | Supported | Supported |
| UNION ALL | Supported | Supported | Supported |
| INTERSECT | Supported | Not natively (use JOIN) | Supported |
Best Practices & Optimization Tips
- Use UNION ALL when duplicates are acceptable for better performance.
- Ensure columns and data types match to avoid conversion overhead.
- Use indexes on columns involved in set operations.
- For MySQL, emulate INTERSECT using INNER JOINs.
When to Use vs When to Avoid
Use UNION and INTERSECT When:
- Combining data from multiple similar queries.
- Finding common or distinct values across datasets.
Avoid When:
- Complex transformations can be handled with joins instead.
- Working with extremely large datasets without optimization.
Conclusion & Key Takeaways
UNION and INTERSECT are powerful SQL set operations for combining and comparing data. Using them correctly improves query flexibility and reporting capabilities.
Key Points:
- UNION merges results; UNION ALL keeps duplicates.
- INTERSECT finds common rows between queries.
- Optimize with indexes and choose UNION ALL when possible.
FAQ
1. What is the difference between UNION and UNION ALL?
UNION removes duplicates; UNION ALL keeps them and is faster.
2. Can I use different column names?
Yes, but the number and types of columns must match.
3. Does MySQL support INTERSECT?
Not natively; use INNER JOIN to emulate.
4. Which is faster, UNION or JOIN?
Depends on the use case; JOINs are often faster for combining related tables.
5. Can I use ORDER BY with UNION?
Yes, but only at the end of the combined query.
6. Do UNION and INTERSECT work with different data types?
Columns must be type-compatible.
7. Can I combine more than two queries?
Yes, you can chain multiple UNIONs or INTERSECTs.
8. How to remove duplicates in INTERSECT?
INTERSECT removes duplicates by default.
9. Are these operations standard SQL?
Yes, but INTERSECT isn’t supported in MySQL without emulation.
10. Should I use UNION or UNION ALL for large datasets?
Prefer UNION ALL when duplicates are acceptable for better performance.