Subqueries

-- ===== Scalar subquery =====
-- Returns a single value; usable wherever a value goes.
SELECT name, (SELECT AVG(total) FROM orders) AS avg_total
FROM users;

-- ===== IN / NOT IN subquery =====
SELECT id, name
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- Beware: NULL inside the IN list makes NOT IN return UNKNOWN. Prefer NOT EXISTS.

-- ===== EXISTS / NOT EXISTS =====
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Often faster than IN on large sets; planner handles correlation cleanly.

-- ===== ANY / ALL =====
SELECT id FROM orders WHERE total > ALL (SELECT credit_limit FROM users);
SELECT id FROM orders WHERE user_id = ANY (ARRAY[1, 2, 3]);

-- ===== Derived table (subquery in FROM) =====
SELECT u.name, t.total_spent
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
) t ON t.user_id = u.id
WHERE t.total_spent > 100;

-- ===== CTE (WITH) =====
WITH big_spenders AS (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 100
)
SELECT u.name, b.total_spent
FROM users u
JOIN big_spenders b ON b.user_id = u.id;

-- CTEs read top-down. Postgres 12+ inlines them (treats them like derived tables)
-- unless you use MATERIALIZED. Use MATERIALIZED to force a temp result:
WITH MATERIALIZED big AS (
  SELECT * FROM orders WHERE total > 100
)
SELECT * FROM big WHERE created_at >= now() - interval '1 day';

-- ===== Recursive CTE =====
WITH RECURSIVE org AS (
  SELECT id, manager_id, name, 1 AS depth FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id, e.name, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY depth;

-- ===== LATERAL =====
-- Subquery on the right side of a join that can reference left columns.
SELECT u.name, recent.total, recent.created_at
FROM users u,
LATERAL (
  SELECT total, created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) recent;

-- LATERAL is great for 'per-row top-N' and 'expand JSON arrays'.

-- ===== Correlated subquery =====
SELECT u.id, u.name,
       (SELECT MAX(total) FROM orders o WHERE o.user_id = u.id) AS biggest
FROM users u;

-- Often slower than the equivalent JOIN + GROUP BY; verify with EXPLAIN.

-- ===== When to use each =====
-- IN / EXISTS         filtering rows by existence
-- Derived table       compute aggregates then join
-- CTE                 readability + recursion
-- LATERAL             per-row computations / expansions
-- Correlated subquery occasional inline lookup; check perf

-- ===== Patterns to internalise =====
-- - EXISTS over IN for nullable column inputs
-- - CTEs for readability, but check EXPLAIN
-- - LATERAL for top-N-per-group
-- - Inline aggregate via derived table; do not repeat the same SUM twice in SELECT

-- ===== Pitfalls =====
-- - NOT IN with a NULL inside -> always false
-- - Correlated subqueries firing per row on huge datasets
-- - CTEs that PREVENT predicate pushdown if MATERIALIZED needlessly
-- - Recursive CTEs without a depth limit -> infinite loops

SELECT *
FROM users
WHERE id IN (SELECT user_id FROM posts GROUP BY user_id HAVING count(*) > 10);