SQL CTEs: the WITH clause

A SQL CTE (Common Table Expression) is a named temporary result set defined with the `WITH` clause and referenced later in the same query. CTEs make complex queries readable by breaking them into named steps, and can be referenced multiple times or made recursive.

Why use a CTE

CTEs replace nested subqueries with named, top-to-bottom steps that read like a pipeline. You can chain several: WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b.

CTE vs subquery

Functionally similar, but a CTE is named and can be referenced more than once, which improves readability and avoids repeating a subquery.

Example (SQL)

WITH region_totals AS (
  SELECT region, SUM(amount) AS total_sales
  FROM sales
  GROUP BY region
)
SELECT region, total_sales
FROM region_totals
WHERE total_sales > 1000
ORDER BY total_sales DESC;

Defines region_totals as a CTE, then filters and sorts it — clearer than a nested subquery.

Run this example in the free online PySpark compiler

Frequently asked questions

What is a CTE in SQL?

A Common Table Expression: a named temporary result set defined with WITH and used within the same query to make it more readable.

What is the difference between a CTE and a subquery?

Both produce a temporary result, but a CTE is named and can be referenced multiple times and made recursive, improving readability.

What is a recursive CTE?

A CTE that references itself (WITH RECURSIVE ...), used for hierarchical or sequential data such as org charts or generating date series.

Practice challenges

• Total Sales per Region using CTEs

Open the free PySpark compiler · Data Engineering challenges · Data Engineering jobs