How to Use CTEs in SQL (WITH Clause Guide)

Introduction

A CTE (Common Table Expression) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE. Think of it as a named subquery that makes complex SQL readable.

Basic CTE Syntax

Define a CTE with the WITH keyword, give it a name, and reference it like a table.

-- Basic CTE:
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE active = true
  AND last_login > CURRENT_DATE - INTERVAL '30 days'
)
SELECT * FROM active_users
WHERE email LIKE '%@gmail.com';

Multiple CTEs (Chaining)

Define multiple CTEs separated by commas. Each CTE can reference the ones defined before it.

WITH
-- CTE 1: Get monthly revenue
monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
  FROM orders
  WHERE created_at >= '2026-01-01'
  GROUP BY DATE_TRUNC('month', created_at)
),
-- CTE 2: Calculate month-over-month growth
revenue_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
      * 100.0 / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
  FROM monthly_revenue
)
-- Final query
SELECT * FROM revenue_growth
ORDER BY month;

Recursive CTE (Hierarchical Data)

Recursive CTEs traverse hierarchical data like org charts, category trees, and bill of materials.

-- Organization hierarchy:
WITH RECURSIVE org_chart AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees under each manager
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  REPEAT('  ', level - 1) || name AS org_tree,
  level
FROM org_chart
ORDER BY level, name;

CTE with INSERT, UPDATE, DELETE

CTEs work with data modification statements, not just SELECT.

-- Delete using CTE:
WITH inactive_users AS (
  SELECT id FROM users
  WHERE last_login < CURRENT_DATE - INTERVAL '365 days'
  AND subscription_status = 'cancelled'
)
DELETE FROM users
WHERE id IN (SELECT id FROM inactive_users);

-- Insert using CTE:
WITH monthly_stats AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', created_at)
)
INSERT INTO reports (month, order_count, total_revenue)
SELECT month, orders, revenue FROM monthly_stats;

CTE vs Subquery: When to Use Which

CTEs and subqueries often produce the same result but differ in readability and sometimes performance.

-- Subquery version (harder to read):
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS rn
  FROM (
    SELECT * FROM employees WHERE active = true
  ) active_emp
) ranked
WHERE rn <= 3;

-- CTE version (much clearer):
WITH active_employees AS (
  SELECT * FROM employees WHERE active = true
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY department ORDER BY salary DESC
    ) AS rn
  FROM active_employees
)
SELECT * FROM ranked WHERE rn <= 3;

Best Practices

• Use CTEs to break complex queries into named, logical steps
• Prefer CTEs over deeply nested subqueries for readability
• Add a termination condition to recursive CTEs to prevent infinite loops
• Use multiple CTEs (chaining) instead of one giant CTE
• In SQL Server, always add a semicolon before WITH to avoid syntax errors

Generate SQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct SQL query instantly.

Frequently Asked Questions