SQL CTE (WITH Clause)

A SQL CTE (common table expression) is a named temporary result defined with the WITH clause at the start of a query. It does the same job as a subquery in FROM but reads top to bottom, which makes long queries much easier to follow.

A Simple CTE

First compute spending per customer, then join it to names.

WITH spend AS (
  SELECT o.CustomerID, SUM(o.Quantity * p.Price) AS total
  FROM Orders o JOIN Products p ON p.ProductID = o.ProductID
  GROUP BY o.CustomerID
)
SELECT c.CustomerName, s.total
FROM spend s JOIN Customers c ON c.CustomerID = s.CustomerID
ORDER BY s.total DESC;

CustomerName   | total
---------------+------
Blue Sky Foods | 270
Harbor Goods   | 264
Golden Grains  | 240
Alpha Traders  | 237.5
Evergreen Co   | 170
Cedar Market   | 150.5
Indigo Retail  | 90
(7 rows)

Multiple CTEs

Separate several CTEs with commas; later ones can use earlier ones. Here we filter by a window function, which needs this two-step approach.

WITH ranked AS (
  SELECT Category, ProductName, Price,
         ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Price DESC) AS rn
  FROM Products
),
top_per_cat AS (
  SELECT * FROM ranked WHERE rn = 1
)
SELECT Category, ProductName, Price FROM top_per_cat ORDER BY Price DESC;

Category   | ProductName  | Price
-----------+--------------+------
Beverages  | Coffee Beans | 22
Oils       | Olive Oil    | 18.75
Snacks     | Almonds      | 15
Grains     | Basmati Rice | 12.5
Sweeteners | Honey        | 11.5
(5 rows)

Recursive CTE

WITH RECURSIVE lets a CTE refer to itself, useful for hierarchies and number or date series. This generates the first 5 days of June. SQL Server writes just WITH.

WITH RECURSIVE days(d) AS (
  SELECT '2026-06-01'
  UNION ALL
  SELECT date(d, '+1 day') FROM days WHERE d < '2026-06-05'
)
SELECT d FROM days;

d
----------
2026-06-01
2026-06-02
2026-06-03
2026-06-04
2026-06-05
(5 rows)

CTE vs Subquery

• CTEs are easier to read and can be referenced several times
• Performance is usually the same; the optimizer inlines them
• Only CTEs can be recursive

Frequently asked questions

Is a CTE faster than a subquery?

Usually they perform the same. Choose CTEs for readability. PostgreSQL 12+ and others inline simple CTEs automatically.

Can I use a CTE with UPDATE or DELETE?

Yes, in most databases a WITH clause can precede INSERT, UPDATE or DELETE statements.

Does a CTE create a temporary table?

No, it exists only while the single statement runs. For reuse across statements, create a temporary table or a view.