What Is a CTE in SQL? A Visual Guide with Examples (2026)

Ever stared at a SQL query with so many nested subqueries it looked like a Russian nesting doll of confusion? Common Table Expressions (CTEs) are SQL's way of saying: “let's organize this mess.” Here's everything you need to know.

What Is a CTE (Common Table Expression)?

A CTE is a temporary, named result set that you define at the top of a query using the WITH keyword. It exists only for the duration of that single query. Once the query finishes, the CTE is gone.

Here's the simplest possible CTE:

WITH active_suspects AS (
  SELECT name, age, last_seen
  FROM suspects
  WHERE status = 'active'
)
SELECT name, age
FROM active_suspects
WHERE age > 30;

Nothing fancy. You define a named result set with WITH, then use it in your main query like any other table.

How CTEs Work: The Execution Flow

A CTE goes through four stages during query execution. Understanding this lifecycle helps you know exactly what's happening under the hood.

The key takeaway: a CTE is not stored anywhere. It's not a table, not a view, not a temp table. It lives and dies within a single statement.

CTE Syntax Breakdown

Let's break down every part of a CTE. Each keyword and clause has a specific job:

Notice the pattern: everything between AS ( and ) is just a normal SELECT query. You can put anything there that you'd normally write in a subquery.

Why Use CTEs? The Problem They Solve

The biggest reason to use CTEs: readability. Complex queries with nested subqueries become nearly impossible to debug. CTEs let you name each step, making your logic self-documenting.

SELECT name, total_appearances
FROM (
  SELECT s.name,
    (SELECT COUNT(*)
     FROM crime_scenes cs
     WHERE cs.suspect_id = s.id
     AND cs.crime_date > '1986-01-01'
    ) AS total_appearances
  FROM suspects s
  WHERE s.status = 'active'
) AS active_with_counts
WHERE total_appearances > 3
ORDER BY total_appearances DESC;

WITH active_suspects AS (
  SELECT id, name
  FROM suspects
  WHERE status = 'active'
),
scene_counts AS (
  SELECT suspect_id, COUNT(*) AS total_appearances
  FROM crime_scenes
  WHERE crime_date > '1986-01-01'
  GROUP BY suspect_id
)
SELECT a.name, sc.total_appearances
FROM active_suspects a
JOIN scene_counts sc ON a.id = sc.suspect_id
WHERE sc.total_appearances > 3
ORDER BY sc.total_appearances DESC;

If you want to practice writing cleaner queries with real data, SQLNoir's detective cases let you solve mysteries with real SQL, including CTEs for organizing complex investigations.

Multiple CTEs in One Query

You can define multiple CTEs in a single query by separating them with commas. Later CTEs can reference earlier ones, letting you build up complex logic step by step.

WITH recent_crimes AS (
  SELECT id, location, crime_date
  FROM crime_scenes
  WHERE crime_date >= '1986-08-01'
),
suspects_at_scenes AS (
  SELECT s.name, s.id AS suspect_id, rc.location
  FROM suspects s
  JOIN recent_crimes rc ON s.scene_id = rc.id
),
repeat_offenders AS (
  SELECT suspect_id, name, COUNT(*) AS scene_count
  FROM suspects_at_scenes
  GROUP BY suspect_id, name
  HAVING COUNT(*) > 1
)
SELECT name, scene_count
FROM repeat_offenders
ORDER BY scene_count DESC;

SELECT name, COUNT(*) AS scene_count
FROM suspects s
JOIN crime_scenes cs ON s.scene_id = cs.id
WHERE cs.crime_date >= '1986-08-01'
  AND s.id IN (
    SELECT suspect_id
    FROM suspects s2
    JOIN crime_scenes cs2
      ON s2.scene_id = cs2.id
    WHERE cs2.crime_date >= '1986-08-01'
    GROUP BY suspect_id
    HAVING COUNT(*) > 1
  )
GROUP BY name
ORDER BY scene_count DESC;

WITH recent_crimes AS (...),
suspects_at_scenes AS (...),
repeat_offenders AS (...)
SELECT name, scene_count
FROM repeat_offenders
ORDER BY scene_count DESC;

Recursive CTEs: Following the Trail

Recursive CTEs reference themselves. They're perfect for hierarchical data like org charts, file systems, or, in our case, following a chain of criminal associates.

A recursive CTE has two parts: the anchor query (the starting point) and the recursive query (the part that references the CTE itself).

WITH RECURSIVE associate_chain AS (
  -- Anchor: Start with the primary suspect
  SELECT id, name, associate_of, 1 AS depth
  FROM suspects
  WHERE name = 'Victor Malone'

  UNION ALL

  -- Recursive: Find each associate's associates
  SELECT s.id, s.name, s.associate_of, ac.depth + 1
  FROM suspects s
  JOIN associate_chain ac ON s.associate_of = ac.id
  WHERE ac.depth < 5  -- Safety limit
)
SELECT name, depth
FROM associate_chain
ORDER BY depth;

CTE vs Subquery: When to Use Each

CTEs and subqueries often produce identical results. The choice usually comes down to readability and reuse. Here's a quick reference:

The performance question comes up a lot. In most databases, the query optimizer treats CTEs and subqueries the same way. The execution plan is usually identical. Pick whichever makes your code clearer.

Common CTE Mistakes (And How to Fix Them)

1. Forgetting the comma between multiple CTEs

WITH first_cte AS (
  SELECT * FROM suspects
)
WITH second_cte AS (  -- ERROR!
  SELECT * FROM crime_scenes
)
SELECT * FROM first_cte
JOIN second_cte ON ...;

WITH first_cte AS (
  SELECT * FROM suspects
),  -- Just a comma!
second_cte AS (
  SELECT * FROM crime_scenes
)
SELECT * FROM first_cte
JOIN second_cte ON ...;

2. Trying to use a CTE in a separate statement

WITH my_cte AS (
  SELECT * FROM suspects WHERE status = 'active'
);

-- This is a SEPARATE statement. my_cte doesn't exist here!
SELECT * FROM my_cte;  -- ERROR: relation "my_cte" does not exist

3. Infinite recursion without an exit condition

-- Add a depth column and filter on it
WITH RECURSIVE chain AS (
  SELECT id, name, 1 AS depth FROM suspects WHERE id = 1
  UNION ALL
  SELECT s.id, s.name, c.depth + 1
  FROM suspects s JOIN chain c ON s.associate_of = c.id
  WHERE c.depth < 10  -- Stop after 10 levels
)
SELECT * FROM chain;

Test Your CTE Knowledge

FAQ