SQL Set Operators - Combining Queries Tutorial
Quick Answer
SQL set operators allow you to combine results from multiple SELECT queries into a single result set. The main operators are UNION, INTERSECT, and EXCEPT, which help merge, find common rows, or exclude rows between queries respectively.
Learning Objectives
- Understand what SQL set operators are and when to use them.
- Learn the syntax and behavior of UNION, INTERSECT, and EXCEPT operators.
- Practice combining multiple SELECT queries using set operators.
Introduction
In SQL, you often need to combine results from multiple queries into a single result set. This is where set operators come in handy.
Set operators allow you to merge, find common rows, or exclude rows between query results efficiently without complex joins or subqueries.
Set operators help you think about query results as sets, enabling powerful data combinations.
Understanding SQL Set Operators
SQL set operators work on the result sets of two or more SELECT statements. They treat these results as sets and perform operations like union, intersection, or difference.
The three main set operators are UNION, INTERSECT, and EXCEPT. Each serves a different purpose in combining query results.
- UNION combines distinct rows from both queries.
- INTERSECT returns rows common to both queries.
- EXCEPT returns rows from the first query that are not in the second.
Using UNION and UNION ALL
The UNION operator merges the results of two queries and removes duplicate rows.
If you want to include duplicates, use UNION ALL, which is faster because it skips the duplicate elimination step.
- Both queries must have the same number of columns.
- Corresponding columns must have compatible data types.
- Column names in the result set come from the first query.
Example of UNION
Suppose you have two tables, Employees_US and Employees_Europe, and want a combined list of all employees without duplicates.
SQL Code Example
```sql SELECT EmployeeID, Name FROM Employees_US UNION SELECT EmployeeID, Name FROM Employees_Europe; ```
Using INTERSECT
INTERSECT returns only the rows that appear in both query results.
This operator is useful when you want to find common records between two datasets.
- Both queries must have the same number of columns with compatible types.
- Duplicates are removed in the result.
Example of INTERSECT
Find employees who work in both US and Europe offices.
SQL Code Example
```sql SELECT EmployeeID, Name FROM Employees_US INTERSECT SELECT EmployeeID, Name FROM Employees_Europe; ```
Using EXCEPT
EXCEPT returns rows from the first query that do not appear in the second query.
It is useful for finding differences between datasets.
- Both queries must have the same number of columns with compatible types.
- Duplicates are removed in the result.
Example of EXCEPT
Find employees who work in the US office but not in Europe.
SQL Code Example
```sql SELECT EmployeeID, Name FROM Employees_US EXCEPT SELECT EmployeeID, Name FROM Employees_Europe; ```
Important Rules and Considerations
When using set operators, keep in mind these important rules:
- All SELECT statements must have the same number of columns.
- Corresponding columns must have compatible data types.
- Column names in the final result come from the first SELECT statement.
- ORDER BY can only be applied once at the end of the combined query.
- Use parentheses to control the order of operations when combining multiple set operators.
Practical Example
This query combines employees from US and Europe tables, removing duplicates.
This query returns employees who work in both US and Europe offices.
This query returns employees who work only in the US office.
Examples
SELECT EmployeeID, Name FROM Employees_US
UNION
SELECT EmployeeID, Name FROM Employees_Europe;This query combines employees from US and Europe tables, removing duplicates.
SELECT EmployeeID, Name FROM Employees_US
INTERSECT
SELECT EmployeeID, Name FROM Employees_Europe;This query returns employees who work in both US and Europe offices.
SELECT EmployeeID, Name FROM Employees_US
EXCEPT
SELECT EmployeeID, Name FROM Employees_Europe;This query returns employees who work only in the US office.
Best Practices
- Ensure all SELECT queries have matching column counts and compatible data types.
- Use UNION ALL when duplicates are acceptable for better performance.
- Apply ORDER BY only once at the end of the combined query.
- Use parentheses to clarify complex combinations of set operators.
- Test queries individually before combining to verify results.
Common Mistakes
- Mismatching the number of columns in combined SELECT statements.
- Using incompatible data types across queries.
- Expecting column names from the second or later queries to appear in the result.
- Applying ORDER BY inside individual SELECT statements instead of at the end.
- Confusing UNION with UNION ALL and expecting duplicates to be removed.
Hands-on Exercise
Combine Customer Lists
Write a query to combine two customer tables using UNION and remove duplicates.
Expected output: A combined list of unique customers from both tables.
Hint: Ensure both SELECT statements have the same columns and data types.
Find Common Products
Use INTERSECT to find products that exist in both the inventory and sales tables.
Expected output: A list of products present in both tables.
Hint: Select matching columns from both tables with the same data types.
Identify Exclusive Orders
Use EXCEPT to find orders present in the online_orders table but not in the shipped_orders table.
Expected output: Orders that have not yet been shipped.
Hint: Select order IDs and ensure column compatibility.
Interview Questions
What is the difference between UNION and UNION ALL?
InterviewUNION removes duplicate rows from the combined result set, while UNION ALL includes all rows, including duplicates, making it faster.
Can you use ORDER BY in each SELECT statement when using set operators?
InterviewNo, ORDER BY can only be applied once at the end of the entire combined query.
What requirements must be met to use set operators between queries?
InterviewAll queries must have the same number of columns with compatible data types, and the columns are combined in order.
MCQ Quiz
1. What is the best first step when learning Combining Queries?
A. Understand the purpose and basic idea
B. Skip directly to advanced implementation
C. Ignore examples and practice
D. Memorize terms without context
Correct answer: A
Starting with the purpose and basic idea makes later examples and practice easier to understand.
2. Which activity helps reinforce Combining Queries?
A. Reading once without practice
B. Building or writing a small practical example
C. Avoiding review questions
D. Skipping the summary
Correct answer: B
A small practical example helps connect the topic to real usage.
3. Which statement is most accurate about this topic?
A. SQL set operators allow you to combine results from multiple SELECT queries into a single result set.
B. Combining Queries never needs examples
C. Combining Queries is unrelated to practical work
D. Combining Queries should be learned without checking results
Correct answer: A
The correct option is based on the available topic explanation.
Key Takeaways
- Set operators combine results from two or more SELECT queries.
- UNION merges distinct rows, while UNION ALL includes duplicates.
- INTERSECT returns only rows common to all queries.
- EXCEPT returns rows from the first query not found in the second.
- All combined queries must have the same number of columns with compatible data types.
Summary
SQL set operators are powerful tools to combine results from multiple queries efficiently.
UNION, INTERSECT, and EXCEPT each serve distinct purposes: merging, finding common rows, and excluding rows respectively.
Understanding their syntax and rules helps write cleaner, more effective SQL queries.
Frequently Asked Questions
Can I combine more than two queries using set operators?
Yes, you can chain multiple queries using set operators like UNION multiple times, but ensure all queries have matching columns.
What happens if the columns have different data types?
SQL will raise an error if the corresponding columns in combined queries have incompatible data types.
Is UNION faster than UNION ALL?
No, UNION ALL is faster because it does not remove duplicates, which requires extra processing.
