SQL Set Operation Column Compatibility

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

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;