Inline Query Set Union (UNION vs UNION ALL)

-- UNION: combines and removes duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL: combines, keeps duplicates (faster)
SELECT product_id, 'sale' AS source FROM sold_items
UNION ALL
SELECT product_id, 'return' AS source FROM returned_items;

-- Rules:
-- Same number of columns in all SELECT statements
-- Compatible data types in corresponding columns
-- Column names taken from first SELECT

-- Column aliases apply to first SELECT
SELECT id, name, 'user' AS type FROM users
UNION ALL
SELECT id, company_name, 'vendor' FROM vendors
ORDER BY name;

-- INTERSECT (rows in both): PostgreSQL/SQL Server
SELECT user_id FROM web_visitors
INTERSECT
SELECT user_id FROM mobile_visitors;

-- EXCEPT/MINUS (rows in first but not second)
SELECT user_id FROM all_users
EXCEPT
SELECT user_id FROM premium_users;

-- Unified activity timeline for a user
SELECT
  'order'           AS activity_type,
  o.id::TEXT        AS reference_id,
  'Placed order: ' || o.order_number AS description,
  o.total_amount    AS amount,
  o.created_at      AS occurred_at
FROM orders o
WHERE o.user_id = 42

UNION ALL

SELECT
  'review',
  r.id::TEXT,
  'Reviewed: ' || p.name,
  NULL,
  r.created_at
FROM reviews r
JOIN products p ON r.product_id = p.id
WHERE r.user_id = 42

UNION ALL

SELECT
  'support_ticket',
  t.id::TEXT,
  'Opened ticket: ' || t.subject,
  NULL,
  t.created_at
FROM support_tickets t
WHERE t.user_id = 42

ORDER BY occurred_at DESC
LIMIT 50;