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;