06 — Subqueries

-- Orders above the average order amount.
-- The scalar subquery (SELECT AVG(...)) executes ONCE (uncorrelated),
-- its result is cached, and the outer query compares every row to that constant.
SELECT id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- Each order with the overall average attached for comparison.
-- The subquery runs once; the cached value is attached to every output row.
SELECT id,
       amount,
       (SELECT AVG(amount) FROM orders) AS overall_avg  -- scalar in SELECT list
FROM orders;

-- Per-customer order count via a CORRELATED scalar subquery.
-- The subquery references c.id from the outer row → re-executes per customer.
-- For 1M customers this is 1M subquery executions unless the planner decorrelates it.
SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Row constructor comparison: (customer_id, amount) = (1, 99.50)
-- Expands to: customer_id = 1 AND amount = 99.50
-- Lexicographic, AND-connected → any NULL makes the whole predicate UNKNOWN.
SELECT * FROM orders
WHERE (customer_id, amount) = (1, 99.50);

-- Row subquery form — find orders matching customer 1's first order exactly
SELECT * FROM orders
WHERE (customer_id, amount) = (
  SELECT customer_id, amount FROM orders WHERE id = 1
);

-- Customers who have placed at least one order.
-- IN expands to: c.id = o1.customer_id OR c.id = o2.customer_id OR ...
-- The planner typically implements this as a semi-join (hash or index).
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- ❌ WRONG: Returns ZERO rows if ANY customer_id in the subquery is NULL.
-- x NOT IN (a, b, NULL) expands to: x <> a AND x <> b AND x <> NULL
-- x <> NULL is UNKNOWN → the entire AND chain becomes UNKNOWN → WHERE keeps nothing.
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Option 1: filter NULLs out of the subquery (works, but verbose)
SELECT * FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- Option 2: NOT EXISTS — NULL-safe, planner uses hash anti-join, short-circuits.
-- The subquery returns no rows for unmatched customers → NOT EXISTS = TRUE.
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Option 3: IS DISTINCT FROM ALL — NULL-safe "not equal to all" (PostgreSQL)
SELECT * FROM customers c
WHERE c.id IS DISTINCT FROM ALL (SELECT customer_id FROM orders);

-- Orders larger than at least one order from customer 1.
-- Equivalent to: amount > (SELECT MIN(amount) FROM orders WHERE customer_id = 1)
SELECT * FROM orders
WHERE amount > ANY (SELECT amount FROM orders WHERE customer_id = 1);

-- Orders larger than every order from customer 1 (i.e., larger than the max).
-- Equivalent to: amount > (SELECT MAX(amount) FROM orders WHERE customer_id = 1)
SELECT * FROM orders
WHERE amount > ALL (SELECT amount FROM orders WHERE customer_id = 1);

-- = ANY (subquery) is exactly equivalent to IN (subquery).
-- IN is the more common spelling; ANY/ALL matter for non-equality comparisons.
SELECT * FROM orders
WHERE customer_id = ANY (SELECT id FROM customers WHERE status = 'active');
-- Same as: WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');

-- Semi-join: customers who have placed at least one order.
-- EXISTS short-circuits on the FIRST matching row — doesn't collect all matches.
-- The planner can use an index on orders.customer_id to find the first match fast.
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Anti-join: customers who have NOT placed any order.
-- NOT EXISTS = TRUE when the subquery is empty (no matching rows).
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Scalar comparison in WHERE
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders WHERE customer_id = 1);

-- HAVING with a scalar subquery — groups whose total exceeds the grand average
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (SELECT AVG(total) FROM (
  SELECT SUM(amount) AS total FROM orders GROUP BY customer_id
) avg_per_customer);

-- Correlated subquery in SELECT: per-customer most recent order date.
-- Re-executes per outer row. For millions of rows, rewrite as a join + GROUP BY.
SELECT c.name,
       (SELECT MAX(ordered_on) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;

-- Derived table: aggregate per customer, then aggregate per city.
-- SQL forbids nested aggregates (AVG(SUM(x)) is illegal), so we stage the inner SUM.
-- A derived table MUST be aliased (per_customer) — syntax error without it.
SELECT city, AVG(customer_total) AS avg_customer_spend
FROM (
  SELECT c.city, c.id, SUM(o.amount) AS customer_total
  FROM customers c
  JOIN orders o ON c.id = o.customer_id
  GROUP BY c.city, c.id
) per_customer
GROUP BY city
HAVING AVG(customer_total) > 100;

-- Top 3 orders per customer by amount.
-- The LATERAL subquery re-evaluates per customer row, with LIMIT bounding each lookup.
-- LEFT JOIN LATERAL + ON true ensures customers with zero orders still appear (with NULLs).
SELECT c.name, top.id AS order_id, top.amount
FROM customers c
LEFT JOIN LATERAL (
  SELECT id, amount
  FROM orders o
  WHERE o.customer_id = c.id    -- references c.id from the outer (left) table
  ORDER BY amount DESC
  LIMIT 3                        -- bounded per-row: planner can use index on customer_id + amount
) top ON true;

-- Production query: per-customer analytics dashboard.
-- Combines:
--   1. EXISTS semi-join (has recent orders?)
--   2. Correlated scalar subquery (lifetime value)
--   3. LATERAL join (top 3 most recent orders)
--   4. NOT EXISTS anti-join (no returned orders)
SELECT
  c.id,
  c.name,
  c.email,
  -- Correlated scalar: lifetime value. Re-executes per customer.
  (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id = c.id) AS lifetime_value,
  -- Semi-join: has the customer ordered in the last 30 days?
  EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.ordered_on >= CURRENT_DATE - INTERVAL '30 days'
  ) AS active_recently,
  -- Anti-join: no returned orders (status = 'returned').
  -- NOT EXISTS is NULL-safe — doesn't poison like NOT IN.
  NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.status = 'returned'
  ) AS has_no_returns,
  -- LATERAL: top 3 most recent orders, bounded per-row.
  top.recent_order_ids,
  top.recent_amounts
FROM customers c
LEFT JOIN LATERAL (
  SELECT
    array_agg(o.id ORDER BY o.ordered_on DESC) AS recent_order_ids,
    array_agg(o.amount ORDER BY o.ordered_on DESC) AS recent_amounts
  FROM (
    SELECT id, amount, ordered_on
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY ordered_on DESC
    LIMIT 3                                  -- only fetch 3 rows per customer
  ) o
) top ON true
WHERE c.status = 'active'
ORDER BY lifetime_value DESC NULLS LAST      -- NULL lifetime_value (no orders) sorts last
LIMIT 100;

-- Intent: find customers who never ordered product #5.
-- Bug: if orders.customer_id is NULL for ANY matched row (e.g., guest checkout),
-- the subquery returns NULL among its results → NOT IN poisons → ZERO rows returned.
SELECT * FROM customers c
WHERE c.id NOT IN (
  SELECT o.customer_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE oi.product_id = 5
);

-- NOT EXISTS: NULL-safe anti-join. The subquery returning empty = TRUE.
-- The planner can use a hash anti-join on (customer_id, product_id).
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = c.id     -- correlated: references outer c.id
    AND oi.product_id = 5
);

-- Per-customer order count via correlated scalar subquery.
-- For 1M customers: 1M subquery executions. The planner may not decorrelate it
-- (especially if the subquery has LIMIT, DISTINCT, or volatile functions).
SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- LEFT JOIN + GROUP BY: single pass over orders, hash-aggregated by customer_id.
-- The planner does ONE scan of orders + ONE hash aggregation — O(N), not O(N*M).
SELECT c.name,
       COALESCE(o.order_count, 0) AS order_count   -- NULL for customers with zero orders
FROM customers c
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) o ON o.customer_id = c.id;

SELECT * FROM customers c
WHERE c.id NOT IN (
  SELECT customer_id FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE oi.product_id = 5
);