SQL CTEs vs Subqueries: Writing Clean and readable Database Queries

Structuring complex logic is a primary challenge in SQL development. Both subqueries and Common Table Expressions (CTEs) allow you to write nested logic, but they differ significantly in readability, maintenance, and optimization characteristics.

What is a CTE?

A Common Table Expression is a temporary result set defined using the WITH clause. You reference it within your main query like a standard database table. It structures code top-down instead of bottom-up.

WITH SalesByRep AS (
  SELECT CustomerID, SUM(TotalAmount) as TotalSpend
  FROM Orders
  GROUP BY CustomerID
)
SELECT c.FirstName, s.TotalSpend
FROM Customer c
INNER JOIN SalesByRep s ON c.CustomerID = s.CustomerID;

Performance and Execution Plans

In many modern RDBMS (like PostgreSQL, SQL Server, and SQLite), simple CTEs perform similarly to subqueries because the engine flattens them before execution. However, CTEs serve as clean documentation and prevent nested subquery spaghetti code.

When to Choose a CTE vs a Subquery

Use a subquery when the logic is short, used exactly once, and does not need a name for clarity. Use a CTE when: (1) the same result set is referenced more than once in the query, (2) the logic is complex enough to deserve a descriptive name, or (3) you are building a multi-step transformation where each step depends on the previous one. CTEs make that dependency chain explicit and readable.

Recursive CTEs: Traversing Hierarchical Data

-- Traverse an org chart from CEO down to leaves
WITH RECURSIVE OrgHierarchy AS (
  -- Anchor: start with the CEO (no manager)
  SELECT EmployeeID, Name, ManagerID, 0 as Level
  FROM Employee
  WHERE ManagerID IS NULL

  UNION ALL

  -- Recursive: add direct reports
  SELECT e.EmployeeID, e.Name, e.ManagerID, oh.Level + 1
  FROM Employee e
  INNER JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
)
SELECT * FROM OrgHierarchy ORDER BY Level;

A recursive CTE has two parts joined by UNION ALL: the anchor member (the starting rows) and the recursive member (how to find the next set of rows given the current set). Execution continues until the recursive member produces no new rows. This is the only SQL construct that can traverse arbitrary-depth hierarchies without knowing the depth in advance.

Multiple CTEs in a Single Query

WITH
HighValueOrders AS (
  SELECT CustomerID, SUM(Amount) as TotalSpend
  FROM Orders
  WHERE OrderDate >= '2026-01-01'
  GROUP BY CustomerID
  HAVING SUM(Amount) > 10000
),
CustomerDetails AS (
  SELECT c.CustomerID, c.Name, c.Region
  FROM Customers c
  WHERE c.Status = 'active'
)
SELECT cd.Name, cd.Region, hvo.TotalSpend
FROM CustomerDetails cd
INNER JOIN HighValueOrders hvo ON cd.CustomerID = hvo.CustomerID
ORDER BY hvo.TotalSpend DESC;

You can define multiple CTEs in a single WITH clause, separated by commas. Each CTE can reference previously defined CTEs in the same WITH block. This allows you to decompose a complex multi-step transformation into readable, named stages - a pattern that senior data engineers use consistently in production analytical queries.

CTE Materialization in PostgreSQL

In PostgreSQL 12 and later, non-recursive CTEs are 'optimization fences' by default - the optimizer cannot push predicates from the outer query into the CTE. This changed in PostgreSQL 12, which made CTEs inlined by default (not materialized). You can control this explicitly with MATERIALIZED or NOT MATERIALIZED hints. For expensive CTEs referenced multiple times, MATERIALIZED can prevent redundant computation.