SQL CTE (WITH Clause): Chaining, CTE vs Subquery, and Recursive CTEs

A common table expression gives one transformation a name inside one SQL statement. That name is useful when a data pipeline has several grains: detail rows, deduplicated entities, grouped metrics, then a final result.

The name is not stored and does not survive the statement. Treat each CTE as an interface: state what one row represents, select only the columns the next step needs, and verify where row counts change.

Name one transformation with a CTE

The input is one row per order. completed_orders keeps only completed detail rows; the outer query changes the grain to one row per customer.

Input: orders

WITH completed_orders AS (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'completed'
)
SELECT
  customer_id,
  COUNT(*) AS completed_orders,
  SUM(amount) AS completed_revenue
FROM completed_orders
GROUP BY customer_id
ORDER BY customer_id;

Output

Only the statement following WITH can reference completed_orders. If another statement needs the same result, repeat the definition, create a persistent object under an explicit lifecycle, or move the shared logic into the data model. The GROUP BY guide covers the aggregate grain used here.

Chain CTEs by declaring each grain

Later CTEs can reference earlier ones. This pipeline moves from completed order rows, to one row per customer, to ranked customer summaries. The final filter keeps two rows.

Input: orders

WITH completed_orders AS (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'completed'
),
per_customer AS (
  SELECT
    customer_id,
    SUM(amount) AS completed_revenue
  FROM completed_orders
  GROUP BY customer_id
),
ranked_customers AS (
  SELECT
    customer_id,
    completed_revenue,
    ROW_NUMBER() OVER (
      ORDER BY completed_revenue DESC, customer_id
    ) AS revenue_rank
  FROM per_customer
)
SELECT
  customer_id,
  completed_revenue,
  revenue_rank
FROM ranked_customers
WHERE revenue_rank <= 2
ORDER BY revenue_rank;

Output

The final WHERE can see revenue_rank because the window calculation happened in the previous query level. SQL order of operations explains why the wrapper is required.

CTE and derived-table forms can return the same rows

A CTE is not automatically a different algorithm. When a transformation is referenced once, a derived table can express the same logical result. This version returns the same three customer summaries as the first query.

Input: orders

SELECT
  customer_id,
  COUNT(*) AS completed_orders,
  SUM(amount) AS completed_revenue
FROM (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'completed'
) AS completed_orders
GROUP BY customer_id
ORDER BY customer_id;

Output

A CTE is easier to reuse by name later in the same statement. A derived table stays beside the single clause that consumes it. A temporary table has a longer lifecycle and can be indexed, but it also introduces separate statements and cleanup policy. Those are design choices, not universal speed rankings.

Recursive CTEs walk dependencies with a stop rule

Recursion has an anchor query and a recursive member joined with UNION ALL. The path array below is both evidence and a cycle guard: once a job name is in the current path, that route cannot visit it again.

Input: job_dependencies

WITH RECURSIVE dependency_walk AS (
  SELECT
    parent_job AS root_job,
    child_job AS job,
    ARRAY[parent_job, child_job] AS path,
    1 AS depth
  FROM job_dependencies
  WHERE parent_job = 'ingest'

  UNION ALL

  SELECT
    w.root_job,
    d.child_job,
    w.path || d.child_job,
    w.depth + 1
  FROM dependency_walk AS w
  JOIN job_dependencies AS d
    ON d.parent_job = w.job
  WHERE NOT d.child_job = ANY(w.path)
    AND w.depth < 10
)
SELECT
  job,
  depth,
  array_to_string(path, ' -> ') AS path
FROM dependency_walk
ORDER BY depth, job;

Output

This query returns paths, not a globally deduplicated node list. In a graph where a job is reachable through two parents, it can appear twice with different paths. Decide whether the result grain is node, edge, or path before adding DISTINCT. Consecutive-run grouping is a different problem covered in SQL gaps and islands.

Recursive CTEs can build a dense date spine

An event table has no row for a quiet day. The recursive spine emits every report date, the aggregate counts existing events, and the left join turns missing dates into explicit zeroes.

Input: report_window

Input: daily_events

WITH RECURSIVE date_spine AS (
  SELECT start_date AS report_date
  FROM report_window

  UNION ALL

  SELECT report_date + 1
  FROM date_spine
  CROSS JOIN report_window
  WHERE report_date < end_date
),
event_counts AS (
  SELECT
    event_date,
    COUNT(*) AS event_count
  FROM daily_events
  GROUP BY event_date
)
SELECT
  s.report_date,
  COALESCE(e.event_count, 0) AS event_count
FROM date_spine AS s
LEFT JOIN event_counts AS e
  ON e.event_date = s.report_date
ORDER BY s.report_date;

Output

PostgreSQL also provides generate_series for this shape. Recursion remains useful when each next row depends on more than a fixed interval. The SQL date-functions guide develops boundary and calendar choices.

FAQ

What is the scope of a CTE?

One statement. A CTE name can be referenced by the main query and by later CTEs in the same WITH clause. It is not visible to the next statement.

Can one CTE reference another?

Yes, when the referenced CTE appears earlier in the list. Recursive self-reference requires WITH RECURSIVE and belongs in the recursive member.

Is a CTE faster than a subquery?

There is no reliable form-only answer. They can describe the same logical operation, and planning depends on the database, version, query, statistics, and explicit materialization choices. Compare plans for the workload that matters.

When should I use a temporary table instead?

Consider one when several statements need the intermediate result, when an index on that result is useful, or when its lifecycle should be controlled independently. Account for creation, cleanup, transaction, and concurrency behavior.

How does a recursive CTE stop?

The recursive member eventually returns no new rows. Encode a business boundary, a cycle check, a depth limit, or a suitable combination. A query over an untrusted graph should not rely on the data being acyclic.