CTEs and Subqueries

Complex analytical questions rarely fit into a single SELECT. DuckDB, following standard SQL, offers two mechanisms for breaking work into named, readable steps: subqueries, which nest one query inside another, and common table expressions (CTEs), which declare intermediate result sets with the WITH clause.

Subqueries

Three placements matter in practice. A scalar subquery returns a single value used in an expression, an IN/EXISTS subquery filters against a set, and a derived table sits in the FROM clause as a temporary named relation:

-- scalar subquery in the SELECT list
SELECT name, salary,
       (SELECT avg(salary) FROM employees) AS company_avg
FROM employees;

-- EXISTS filter
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.id AND o.amount > 100);

-- derived table
SELECT d.dept, d.avg_sal
FROM (SELECT dept, avg(salary) AS avg_sal
      FROM employees GROUP BY dept) d
WHERE d.avg_sal > 60000;

A subquery that references columns of the outer query, like the EXISTS example, is called a correlated subquery; DuckDB evaluates such subqueries efficiently and often flattens them into a join.

Common Table Expressions

A CTE gives an intermediate result a name via WITH, which reads left to right instead of inside out:

WITH dept_avg AS (
    SELECT dept, avg(salary) AS avg_sal, count(*) AS heads
    FROM employees
    GROUP BY dept
)
SELECT e.name, e.dept, e.salary, d.avg_sal,
       e.salary - d.avg_sal AS gap
FROM employees e
JOIN dept_avg d USING (dept)
WHERE e.salary > d.avg_sal;

Both a derived table and a CTE produce an equivalent plan here; the CTE version is simply easier to maintain as the number of steps grows.

Multiple and Chained CTEs

One WITH can define several CTEs separated by commas, and later CTEs may reference earlier ones, building a pipeline of stages:

WITH monthly AS (
    SELECT date_trunc('month', order_date) AS m, sum(amount) AS rev
    FROM orders GROUP BY 1
),
ranked AS (
    SELECT m, rev, rank() OVER (ORDER BY rev DESC) AS rnk
    FROM monthly
)
SELECT m, rev FROM ranked WHERE rnk <= 3;

Recursive CTEs

Adding the RECURSIVE keyword lets a CTE reference itself, which expresses hierarchical walks such as an employee tree or a bill of materials:

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

The base case seeds the result, and the recursive step repeatedly joins back onto the CTE until no new rows appear.

Choosing Between Them

Use subqueries for one-off filters such as IN, EXISTS or a single scalar lookup, and CTEs whenever an intermediate result is named, reused or ordered as a pipeline step. DuckDB materializes or inlines CTEs behind the scenes, so choosing the clearer form rarely costs performance.

Summary

Subqueries express nested logic in three placements, scalar, set-based and as derived tables, while CTEs under WITH turn multi-stage analyses into a labeled pipeline that can chain and even recurse with RECURSIVE. Together they keep analytical SQL modular and readable, and DuckDB optimizes both forms aggressively so readability carries no runtime penalty.