What is a CTE in SQL? Explained in one query

A CTE is a subquery with a name. That is the entire concept. WITH engineers AS (…) defines it, and the main query selects FROM engineers as if a table called engineers existed. It lives for one statement, then it's gone. Everything else people say about CTEs — readability, reuse, recursion — is a consequence of being able to name a step.

The one query that explains it

WITH engineers AS (
  SELECT name, salary, hire_date
  FROM employees
  WHERE department = 'Engineering'
)
SELECT name, salary
FROM engineers
WHERE salary > 90000
ORDER BY salary DESC;

Read it top to bottom: first define what "engineers" means (the Engineering rows), then ask the actual question (who earns above 90k). The database sees the same work as a nested subquery — but the human sees a recipe with named ingredients.

Could you write this as one WHERE department = 'Engineering' AND salary > 90000? Absolutely — and for something this small you should. The CTE earns its keep when the steps multiply; the point here is only the shape.

What the name buys you

• Top-to-bottom reading. Subqueries read inside-out: the deepest parenthesis runs first, and your eyes hunt for it. CTEs read like the query executes in your head — step 1, step 2, answer.
• Reuse without repetition. The main query can reference engineers three times without pasting the subquery three times. One definition, one place to fix.
• Recursion. WITH RECURSIVE can walk an org chart of unknown depth. No plain subquery can do this — it is the one thing CTEs can do that nothing else in the language can. (Full walkthrough: Recursive CTEs explained.)

The scoping rule that trips everyone

A CTE exposes only the columns its own SELECT lists. Not the underlying table's columns — its own. This is the single most common CTE error, and it looks like this:

WITH engineers AS (
  SELECT name, salary FROM employees WHERE department = 'Engineering'
)
SELECT name, salary, hire_date   -- ✗ no such column: hire_date
FROM engineers;

hire_date obviously exists in employees — but engineers is not employees. It is the two-column result you defined, and hire_date was not one of them. The fix is mechanical: add hire_date to the CTE's SELECT.

Multiple CTEs = a pipeline with named steps

WITH engineers AS (
  SELECT name, salary FROM employees WHERE department = 'Engineering'
),
seniors AS (
  SELECT name, salary FROM engineers WHERE salary > 90000
)
SELECT COUNT(*) AS senior_count, ROUND(AVG(salary)) AS avg_salary
FROM seniors;

One WITH, commas between definitions, and each CTE can use the ones above it. This is how a gnarly report stays maintainable: filter → enrich → aggregate, each step named, each step testable on its own by changing the final SELECT.

▶ Practice: Give a Query a Name (WITH)

When a plain subquery is fine

Honesty over dogma: not everything needs a CTE.

• A one-line inline filter: WHERE customer_id IN (SELECT customer_id FROM orders WHERE country = 'USA') — naming this buys nothing. (This shape has its own writeup: CTE vs subquery vs temp table.)
• A single derived table used once: FROM (SELECT …) t is fine when it is small and obvious.
• Rule of thumb: the moment you would want to name the step to explain it to a colleague — or reference it twice — it is a CTE.

FAQ

What does CTE stand for?

Common Table Expression. The name is worse than the thing: "named subquery" describes it better than all three words combined.

Can a CTE be used in UPDATE or DELETE?

Yes in most engines — e.g. WITH stale AS (SELECT id FROM …) DELETE FROM t WHERE id IN (SELECT id FROM stale). PostgreSQL goes further and allows data-modifying CTEs themselves; treat that as an advanced tool.

How is a CTE different from a view?

Scope. A view is a saved, named query that lives in the database schema and every query can use. A CTE exists for one statement only. Same idea — naming a query — at two lifetimes.

How is a CTE different from a temp table?

A temp table persists for your whole session and is written to storage; a CTE evaporates when the statement ends. If three separate queries need the same intermediate result, compute it once into a temp table. If one query does, use a CTE.