SQL UNION vs INTERSECT Explained — Real-World Patterns and Pitfalls

-- Scenario: orders are split across two yearly tables.
-- We need a single list of all customers who placed an order in either year.

-- Table: orders_2023
-- | customer_id | customer_email          | order_total |
-- |-------------|-------------------------|-------------|
-- | 101         | alice@example.com       | 250.00      |
-- | 102         | bob@example.com         | 89.99       |
-- | 103         | carol@example.com       | 410.50      |

-- Table: orders_2024
-- | customer_id | customer_email          | order_total |
-- |-------------|-------------------------|-------------|
-- | 102         | bob@example.com         | 120.00      |  -- Bob ordered in both years
-- | 104         | dave@example.com        | 305.75      |
-- | 105         | eve@example.com         | 55.00       |


-- ── EXAMPLE 1: UNION (deduplicates — Bob appears only ONCE) ──────────────────
SELECT customer_id, customer_email
FROM orders_2023

UNION  -- removes duplicate rows before returning results (slower)

SELECT customer_id, customer_email
FROM orders_2024;

-- Output:
-- | customer_id | customer_email    |
-- |-------------|-------------------|
-- | 101         | alice@example.com |
-- | 102         | bob@example.com   |  -- only one row for Bob
-- | 103         | carol@example.com |
-- | 104         | dave@example.com  |
-- | 105         | eve@example.com   |


-- ── EXAMPLE 2: UNION ALL (keeps ALL rows — Bob appears TWICE) ────────────────
SELECT customer_id, customer_email
FROM orders_2023

UNION ALL  -- no deduplication — just appends. Faster.

SELECT customer_id, customer_email
FROM orders_2024;

-- Output:
-- | customer_id | customer_email    |
-- |-------------|-------------------|
-- | 101         | alice@example.com |
-- | 102         | bob@example.com   |  -- first occurrence (2023)
-- | 103         | carol@example.com |
-- | 102         | bob@example.com   |  -- second occurrence (2024)
-- | 104         | dave@example.com  |
-- | 105         | eve@example.com   |


-- ── EXAMPLE 3: UNION with ORDER BY and column alias ─────────────────────────
-- ORDER BY can only appear ONCE — at the very end, after the final SELECT.
-- It applies to the entire combined result, not just one half.
SELECT customer_id, customer_email, order_total, 2023 AS order_year
FROM orders_2023

UNION ALL

SELECT customer_id, customer_email, order_total, 2024 AS order_year
FROM orders_2024

ORDER BY customer_id, order_year;  -- sorts the combined result

-- Output:
-- | customer_id | customer_email    | order_total | order_year |
-- |-------------|-------------------|-------------|------------|
-- | 101         | alice@example.com | 250.00      | 2023       |
-- | 102         | bob@example.com   | 89.99       | 2023       |
-- | 102         | bob@example.com   | 120.00      | 2024       |
-- | 103         | carol@example.com | 410.50      | 2023       |
-- | 104         | dave@example.com  | 305.75      | 2024       |
-- | 105         | eve@example.com   | 55.00       | 2024       |

-- Scenario: A SaaS company wants to identify "sticky" users —
-- customers who were active in BOTH January AND February 2024.
-- These are the users worth targeting for an annual plan upgrade.

-- Table: user_activity
-- | user_id | activity_month | feature_used       |
-- |---------|----------------|--------------------|
-- | 201     | 2024-01        | dashboard          |
-- | 202     | 2024-01        | reports            |
-- | 203     | 2024-01        | dashboard          |
-- | 201     | 2024-02        | api_integration    |
-- | 204     | 2024-02        | dashboard          |
-- | 202     | 2024-02        | reports            |


-- ── EXAMPLE 1: Basic INTERSECT ───────────────────────────────────────────────
-- Find user_ids active in BOTH months
SELECT user_id
FROM user_activity
WHERE activity_month = '2024-01'

INTERSECT  -- only rows that appear in BOTH result sets pass through

SELECT user_id
FROM user_activity
WHERE activity_month = '2024-02';

-- Output:
-- | user_id |
-- |---------|
-- | 201     |  -- active in both January and February
-- | 202     |  -- active in both January and February
-- (user 203 only appears in Jan, user 204 only in Feb — excluded)


-- ── EXAMPLE 2: INTERSECT as a cleaner alternative to IN + subquery ───────────
-- Same result, but compare readability:

-- The subquery way (harder to read at a glance):
SELECT DISTINCT user_id
FROM user_activity
WHERE activity_month = '2024-01'
  AND user_id IN (
      SELECT user_id          -- buried logic — reader must mentally trace this
      FROM user_activity
      WHERE activity_month = '2024-02'
  );

-- The INTERSECT way (reads like the business requirement itself):
SELECT user_id FROM user_activity WHERE activity_month = '2024-01'
INTERSECT
SELECT user_id FROM user_activity WHERE activity_month = '2024-02';

-- Output for both: same two rows — user 201 and 202.
-- INTERSECT wins on readability. Query optimizers often produce the same plan.


-- ── EXAMPLE 3: INTERSECT with multiple columns ───────────────────────────────
-- Find (user_id, feature_used) pairs that appear in BOTH months.
-- i.e. users who used the SAME feature in both Jan AND Feb.
SELECT user_id, feature_used
FROM user_activity
WHERE activity_month = '2024-01'

INTERSECT

SELECT user_id, feature_used
FROM user_activity
WHERE activity_month = '2024-02';

-- Output:
-- | user_id | feature_used |
-- |---------|--------------|
-- | 202     | reports      |  -- User 202 used 'reports' in BOTH months
-- (User 201 is excluded here because they used 'dashboard' in Jan
--  but 'api_integration' in Feb — different feature, so no match)

-- Scenario: A retail analytics team needs a 'VIP Customer Report'.
-- Definition of VIP:
--   (a) Customers who spent > $500 in 2023  OR  > $500 in 2024 (high spenders)
--   AND
--   (b) That combined list must ALSO have made a purchase in the last 30 days
--       (still active — not just historically high-value)

-- Tables available:
-- orders(order_id, customer_id, order_total, order_date)
-- customers(customer_id, full_name, email, signup_date)


-- ── Step 1: Use a CTE to build the 'high spender' pool via UNION ALL ─────────
WITH high_spenders AS (
    -- High spenders from 2023
    SELECT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2023
    GROUP BY customer_id
    HAVING SUM(order_total) > 500  -- only customers whose 2023 total exceeded $500

    UNION  -- plain UNION because we want to deduplicate across years
           -- a customer shouldn't appear twice just for being high-value in both years

    -- High spenders from 2024
    SELECT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2024
    GROUP BY customer_id
    HAVING SUM(order_total) > 500
),

-- ── Step 2: Find recently active customers ───────────────────────────────────
recently_active AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'  -- activity in last 30 days
)

-- ── Step 3: INTERSECT — only customers who are in BOTH groups ────────────────
-- We join back to customers for the display columns
SELECT
    c.customer_id,
    c.full_name,
    c.email
FROM customers c
WHERE c.customer_id IN (
    -- The INTERSECT here is the heart of the query:
    -- high spenders who are also recently active
    SELECT customer_id FROM high_spenders
    INTERSECT
    SELECT customer_id FROM recently_active
)
ORDER BY c.full_name;


-- ── Alternative: explicit parentheses when chaining UNION + INTERSECT ────────
-- Without parentheses, INTERSECT binds first — this is almost never what you want
-- when mixing operators. Always parenthesize.

-- WRONG — INTERSECT runs before UNION due to precedence:
SELECT customer_id FROM orders_2023
UNION
SELECT customer_id FROM orders_2024
INTERSECT                              -- binds to orders_2024 first!
SELECT customer_id FROM recently_active;

-- RIGHT — parentheses make intent unambiguous:
(SELECT customer_id FROM orders_2023
 UNION
 SELECT customer_id FROM orders_2024)
INTERSECT
SELECT customer_id FROM recently_active;  -- now intersects the full union result