In this lesson, you will learn how to combine data from separate SELECT statements into one cohesive result set using UNION and INTERSECT.
Thinking in Sets
So far, you have used JOIN to link tables side-by-side based on common columns. However, sometimes you want to stack data vertically—taking the results of one query and placing them on top of the results of another. To do this, we use Set Operators.
The UNION Operator
UNION combines the output of two queries into one list. Think of it like taking two piles of files and stacking them together. If you have a table of LocalCustomers and a table of InternationalCustomers, you can combine them into a single list of everyone you serve:
SELECT customer_name FROM LocalCustomers
UNION
SELECT customer_name FROM InternationalCustomers;
Crucially, UNION automatically removes duplicates. If a customer happens to be in both lists, they only appear once in your final output. (If you want to keep duplicates, you can use UNION ALL).
The INTERSECT Operator
While UNION gathers everything, INTERSECT looks for the overlap. It returns only the rows that appear in both result sets. Imagine you want to find customers who have purchased both "Hardware" and "Software." You can treat those two separate queries as sets and find their common ground:
SELECT customer_id FROM Orders WHERE category = 'Hardware'
INTERSECT
SELECT customer_id FROM Orders WHERE category = 'Software';
Important Rules
For these operators to work, your queries must be "set-compatible." This means:
- Both queries must return the same number of columns.
- The columns must be in the same order.
- The data types must be compatible (e.g., you can't stack a numeric price column on top of a text name column).
Think of these as "vertical joins"—instead of widening your results with extra columns, you are lengthening your results with extra rows.
Key Takeaway: UNION merges two result sets into one comprehensive list, while INTERSECT isolates only the data points that exist in both sets.