SQL Set Operations
Combine the RESULTS of two separate queries — stacked together, not matched by a shared key the way JOIN works.
What & Why
Every join in the previous series combined two tables side by side, matching rows by a key. Set operations do something different: they combine two queries' results vertically — stacking rows from Query A and Query B together — with no key, no matching condition, nothing except the requirement that both queries return the same number of columns, in compatible types.
This lesson series covers UNION (combine, remove duplicates), UNION ALL (combine, keep duplicates), INTERSECT (keep only rows in both), and EXCEPT (keep only rows in one but not the other) — the four ways two result sets can be combined.
See How It Works
Marketing wants one deduplicated calendar containing every campaign start and end date.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
SELECT start_date AS campaign_date
FROM marketing.campaigns
UNION
SELECT end_date AS campaign_date
FROM marketing.campaigns
WHERE end_date IS NOT NULL
ORDER BY campaign_date;| campaign_date |
|---|
| 2024-01-15 |
| 2024-02-10 |
| 2024-03-12 |
| 2024-03-31 |
| 2024-04-01 |
| 2024-04-15 |
| 2024-05-31 |
| 2024-06-30 |
UNION deduplicates and sorts the four start dates and four end dates.
Practice this concept
Marketing wants one deduplicated calendar of every campaign start and end date.
marketingPrefix tables with marketing.table_name.
campaign_datemarketing.campaigns| Column | Type |
|---|---|
| id | integer |
| name | text |
| channel | text |
| spend | numeric |
| start_date | date |
| end_date | date |
| status | text |
| target_segment | text |
| legacy_id | text |
Sign up free to try it on a real business scenario