08 — Set Operations

-- All cities that appear in either customers or suppliers (deduplicated).
-- UNION sorts/hashes the combined result to remove duplicates: O(N log N) or O(N) memory.
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- All cities including duplicates. UNION ALL just concatenates: O(1) per row.
-- Use when inputs are known-disjoint or duplicates are desired.
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- Cities that have BOTH customers and suppliers (intersection).
SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

-- Cities with customers but NO suppliers (set difference / anti-join).
-- EXCEPT is NULL-safe: (1, NULL) in both inputs → treated as equal → removed.
SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

-- ❌ Reflexive UNION: pays dedup cost even though the two queries can never overlap
-- (different 'kind' values). The sort/hash is wasted work.
SELECT id, name, 'customer' AS kind FROM customers
UNION
SELECT id, name, 'supplier' AS kind FROM suppliers;

-- ✅ UNION ALL: no dedup, O(1) concatenation. Correct when inputs are disjoint.
SELECT id, name, 'customer' AS kind FROM customers
UNION ALL
SELECT id, name, 'supplier' AS kind FROM suppliers;

-- ✅ OK: same column count, compatible types. Result column is named "name" (from first SELECT).
SELECT id, name FROM customers
UNION ALL
SELECT id, company_name FROM suppliers;   -- 'company_name' is ignored; result column = 'name'

-- ❌ ERROR: each SELECT must have the same number of columns.
SELECT id, name FROM customers
UNION ALL
SELECT id FROM suppliers;

-- Type coercion can surprise: integer + text may coerce or error depending on engine.
SELECT 1 UNION SELECT 'a';   -- PostgreSQL: errors; MySQL: coerces to text

-- ORDER BY applies to the ENTIRE combined result, not individual SELECTs.
-- Column names in ORDER BY must match the FIRST input's column names.
SELECT name, 'customer' AS kind FROM customers
UNION ALL
SELECT name, 'supplier' AS kind FROM suppliers
ORDER BY name;                             -- sorts the combined 2-row-per-entity result

-- LIMIT also applies to the whole combined result.
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers
ORDER BY name
LIMIT 10;

-- Limit each branch to 5 rows, then combine and sort the 10-row result.
-- PostgreSQL allows parenthesized set operation branches; standard SQL and other engines vary.
(SELECT name FROM customers ORDER BY name LIMIT 5)
UNION ALL
(SELECT name FROM suppliers ORDER BY name LIMIT 5)
ORDER BY name;

-- EXCEPT ALL: preserves multiplicity. If 'NYC' appears 3x in customers and 1x in suppliers,
-- the result has 'NYC' 2x (3 - 1 = 2). EXCEPT (without ALL) would show 'NYC' 0x (deduped to 1, then removed).
SELECT city FROM customers
EXCEPT ALL
SELECT city FROM suppliers;

-- Build a unified customer view from three sources: CRM, billing, and support.
-- UNION ALL because the sources are disjoint (different ID ranges, tagged by source).
-- A provenance column ('source') tracks origin for debugging and dedup audits.
SELECT
  id,
  email,
  name,
  company,
  'crm' AS source,                           -- provenance: which system this row came from
  created_at
FROM crm.customers
WHERE email IS NOT NULL                       -- drop rows without email (can't dedup on NULL)

UNION ALL

SELECT
  id + 1000000 AS id,                         -- offset to avoid ID collision with CRM
  email,
  billing_contact AS name,
  company_name AS company,
  'billing' AS source,
  registered_at AS created_at
FROM billing.accounts
WHERE email IS NOT NULL

UNION ALL

SELECT
  id + 2000000 AS id,                         -- offset to avoid ID collision with CRM and billing
  email,
  display_name AS name,
  organization AS company,
  'support' AS source,
  first_seen AS created_at
FROM support.users
WHERE email IS NOT NULL

ORDER BY email, source;                       -- group duplicates together for inspection

-- Find products that exist in the catalog but have NEVER been ordered.
-- EXCEPT is a NULL-safe anti-join: (product_id, NULL) in both sides → treated as equal → removed.
-- Unlike NOT IN, EXCEPT doesn't poison on NULL.
SELECT product_id, product_name
FROM (
  SELECT id AS product_id, name AS product_name FROM products
) catalog
EXCEPT
SELECT product_id, NULL AS product_name        -- column count must match; NULL placeholder
FROM (
  SELECT DISTINCT product_id FROM order_items
) ordered

ORDER BY product_id;

-- Symmetric difference: rows in exactly one of two tables (useful for prod vs staging diffs).
-- (A EXCEPT B) UNION (B EXCEPT A) = empty iff A = B (as sets).
(SELECT * FROM prod.users
 EXCEPT
 SELECT * FROM staging.users)
UNION
(SELECT * FROM staging.users
 EXCEPT
 SELECT * FROM prod.users);

-- Intent: combine active and inactive users into one report.
-- Bug: using UNION forces a sort/hash dedup across the entire result.
-- But active and inactive users are DISJOINT (status = 'active' vs 'inactive') —
-- there can be no duplicates between them. The dedup is wasted work.
SELECT id, name, 'active' AS status FROM users WHERE status = 'active'
UNION
SELECT id, name, 'inactive' AS status FROM users WHERE status = 'inactive';

-- UNION ALL: no dedup, O(1) concatenation. Correct because the WHERE clauses
-- partition the table into disjoint sets — no row can satisfy both conditions.
SELECT id, name, 'active' AS status FROM users WHERE status = 'active'
UNION ALL
SELECT id, name, 'inactive' AS status FROM users WHERE status = 'inactive';

-- Full outer join of customers and orders on customer_id.
-- Branch 1: all customers with their matching orders (LEFT JOIN).
-- Branch 2: orders with no matching customer (RIGHT JOIN anti-join: WHERE c.id IS NULL).
-- UNION dedups the overlap (customers with orders appear in both branches).
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL;

SELECT COUNT(*) FROM (
  SELECT city FROM customers
  UNION ALL
  SELECT city FROM suppliers
) all_cities;