Subquery vs JOIN vs CTE — When to Use Which

-- Requirement: list order id, amount, customer city, product category
-- for completed orders in 2024.

-- Version 1: Correlated subquery in SELECT (readable but N+1 risk)
SELECT
    o.id,
    o.amount,
    (SELECT u.city FROM users u WHERE u.id = o.user_id)       AS city,
    (SELECT p.category FROM products p WHERE p.id = o.product_id) AS category
FROM orders o
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01';
-- Risk: two correlated subqueries → two extra lookups per row.

-- Version 2: JOIN (best when you need columns from both tables)
SELECT
    o.id,
    o.amount,
    u.city,
    p.category
FROM orders o
JOIN users    u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01';
-- Clean, one scan per table; optimizer free to choose hash/merge/nested loop.

-- Version 3: CTE (best when logic has multiple named steps)
WITH completed_orders AS (
    SELECT id, user_id, product_id, amount
    FROM orders
    WHERE status = 'completed'
      AND created_at >= '2024-01-01'
)
SELECT
    co.id,
    co.amount,
    u.city,
    p.category
FROM completed_orders co
JOIN users    u ON u.id = co.user_id
JOIN products p ON p.id = co.product_id;

-- When they differ in practice:

-- 1. Need both table's columns → must use JOIN (subquery in WHERE cannot project)
-- This is WRONG (cannot select o.amount AND u.email in WHERE subquery):
SELECT o.id, o.amount
FROM orders o
WHERE o.user_id = (SELECT id, email FROM users WHERE city = 'Mumbai'); -- ERROR

-- 2. Existence check only → EXISTS is cleaner and short-circuits
SELECT o.id, o.amount
FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.city = 'Mumbai');

-- 3. NOT IN vs NOT EXISTS: semantically different with NULLs (see in-exists-any-all)

-- 4. CTE referenced twice: planner may scan source twice unless MATERIALIZED
WITH city_stats AS MATERIALIZED (
    SELECT city, COUNT(*) AS user_count, AVG(salary) AS avg_salary
    FROM employees e JOIN departments d ON d.id = e.department_id
    GROUP BY city
)
SELECT * FROM city_stats WHERE user_count > 10
UNION ALL
SELECT * FROM city_stats WHERE avg_salary > 80000;
-- MATERIALIZED: source scanned once, result reused for both references.