Subqueries & Common Table Expressions

Subqueries and CTEs let you break complex problems into manageable pieces. This lesson covers scalar subqueries, correlated subqueries, EXISTS, CTEs, recursive CTEs, and CTE materialisation behaviour in PostgreSQL.

---

Scalar Subqueries

A scalar subquery returns a single value and can be used anywhere a single value is expected:

-- Compare each order's total to the overall average
SELECT
  order_id,
  total,
  (SELECT AVG(total) FROM orders) AS avg_total,
  total - (SELECT AVG(total) FROM orders) AS diff_from_avg
FROM orders;

In a WHERE Clause

-- Orders above the average total
SELECT order_id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Tip: Scalar subqueries are executed once if they are uncorrelated (no reference to the outer query). PostgreSQL caches the result.

---

Correlated Subqueries

A correlated subquery references a column from the outer query, so it is re-evaluated for each outer row:

-- For each product, find how many orders include it
SELECT
  p.id,
  p.name,
  (
    SELECT COUNT(*)
    FROM order_items oi
    WHERE oi.product_id = p.id
  ) AS order_count
FROM products p;

-- Find employees who earn more than their department average
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

Performance note: Correlated subqueries can be slow on large tables since they may execute once per outer row. Consider rewriting as a JOIN or CTE when performance matters.

---

EXISTS and NOT EXISTS

EXISTS tests whether a subquery returns any rows. It short-circuits — it stops as soon as one row is found:

-- Customers who have at least one order over 500
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 500
);

-- Products that have never been ordered
SELECT p.id, p.name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.product_id = p.id
);

Why SELECT 1? The actual columns selected inside EXISTS do not matter — only the presence or absence of rows.

---

Common Table Expressions (CTEs)

CTEs (introduced with WITH) define named temporary result sets that make queries more readable and maintainable:

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue
ORDER BY month;

Multiple CTEs

Chain multiple CTEs separated by commas:

WITH
  active_customers AS (
    SELECT id, name
    FROM customers
    WHERE status = 'active'
  ),
  recent_orders AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY customer_id
  )
SELECT
  ac.name,
  COALESCE(ro.order_count, 0) AS orders_90d,
  COALESCE(ro.total_spent, 0) AS spent_90d
FROM active_customers ac
LEFT JOIN recent_orders ro ON ro.customer_id = ac.id
ORDER BY spent_90d DESC;

---

Recursive CTEs