Warming up the neural circuits...
Set operations let you combine the results of two or more SELECT statements. By the end of this module you will:
What it is: UNION combines the result sets of two or more SELECT statements into a single result set. By default, it removes duplicate rows (like DISTINCT on the combined result).
Why we use it: When you need to merge data from different tables or queries — "all users from both systems", "all products from multiple categories", "all events from different sources".
When we use it: When combining similar data from different tables, merging results from different time periods, or building unified views from fragmented data.
-- All people (users + admins) — no duplicates
SELECT username AS name FROM users
UNION
SELECT name FROM admins;-- All people, including duplicates
SELECT username AS name FROM users
UNION ALL
SELECT name FROM admins;UNION ALL is faster because it doesn't check for duplicates. Use it when you know there are no duplicates or you don't care.
What it is: INTERSECT returns only rows that appear in both query results. It's the equivalent of "AND" for result sets — the overlap between two datasets.
Why we use it: When finding commonalities — "users who are in both the premium and regular tables", "products that are both in stock and on sale", "customers who bought both product A and product B".
When we use it: When finding overlapping data, validating data consistency across tables, or building intersection-based reports.
-- Users who are also admins
SELECT username FROM users
INTERSECT
SELECT name FROM admins;What it is: EXCEPT returns rows from the first query that are not in the second query. It's the SQL equivalent of "minus" — subtracting one dataset from another.
Why we use it: When finding differences — "users who are NOT admins", "products that have never been ordered", "records in table A but not in table B".
When we use it: When finding missing data, identifying discrepancies between tables, or building exclusion-based reports.
-- Users who are NOT admins
SELECT username FROM users
EXCEPT
SELECT name FROM admins;What it is: Set operations have strict rules — both queries must return the same number of columns with compatible types. The column names in the result come from the first query.
Why we use it: These rules ensure the result set has a consistent structure. Without them, combining incompatible result sets would produce ambiguous or unusable data.
When we use it: Every time you use UNION, INTERSECT, or EXCEPT — you must ensure both queries have matching column structures.
-- Valid: same number of columns, compatible types
SELECT id, username FROM users
UNION
SELECT id, name FROM admins
ORDER BY id;| Operation | Description | Duplicates |
|---|---|---|
UNION | Combine both result sets | Removed |
UNION ALL | Combine both result sets | Kept |
INTERSECT | Rows in BOTH queries | Removed |
EXCEPT | Rows in first but NOT second | Removed |
Rendering diagram…
premium_users table and a regular_users table. Use UNION to get all users.Next up: You've learned how to combine queries. In the next module, you'll learn CASE — SQL's if/else for conditional logic in queries.