SQL Set Operation Column Compatibility
Both queries must return the same number of columns, in compatible types — the one hard rule every set operation shares.
What & Why
Unlike a JOIN, a set operation isn't matching rows by a key — it's stacking them, which means the two queries' output shapes have to line up exactly. Same number of columns, and each corresponding column needs a compatible type (an integer column can typically pair with another integer or numeric column, but not directly with text).
See How It Works
BUSINESS QUESTION
Marketing wants campaign and lead milestones aligned to the same three-column contract.
| 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 |
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
EXAMPLE QUERY
SELECT
name AS entity_name,
start_date::timestamp AS occurred_at,
'campaign'::text AS entity_type
FROM marketing.campaigns
UNION ALL
SELECT
email AS entity_name,
created_at AT TIME ZONE 'UTC' AS occurred_at,
'lead'::text AS entity_type
FROM marketing.leads
ORDER BY occurred_at, entity_name;RESULT — exact output from the displayed Queryflo rows
| entity_name | occurred_at | entity_type |
|---|---|---|
| Spring Launch | 2024-01-15 00:00:00 | campaign |
| ana@example.com | 2024-01-21 09:10:00 | lead |
| ben@example.com | 2024-01-24 12:40:00 | lead |
| Retention Webinar | 2024-02-10 00:00:00 | campaign |
| chloe@example.com | 2024-02-16 08:30:00 | lead |
| Finance Retargeting | 2024-03-12 00:00:00 | campaign |
| dev@example.com | 2024-03-20 17:15:00 | lead |
| Enterprise Search | 2024-04-01 00:00:00 | campaign |
Both branches return the same text, UTC timestamp, and text column shape.
Sign up free to try it on a real business scenario