CTEs: Standalone, Multiple & Nested

Nested subqueries are read from the inside out, which is unnatural. A CTE lets you write the same logic top to bottom, giving each step a name — like writing the steps of a recipe.

Definition

A CTE (Common Table Expression) is a named, temporary result set defined with the WITH keyword at the start of a statement. It exists only for the single statement that immediately follows it, and that statement can refer to it by name just like a table.

• Named — you choose a name (team_totals) and use it in the main query.
• Temporary — nothing is saved in the database; when the statement ends, the CTE disappears.
• Single statement — a CTE cannot be reused by the next query. For that you need a view (later topic).

How the database executes a CTE

Note: Logically a CTE is like a view that lives for one query. SQL Server does not necessarily store the CTE's rows — it usually inlines the CTE into the main query (and may evaluate it again each time it is referenced). It is a readability tool, not a caching tool. To store a result, use a temp table.

Syntax

WITH cte_name [(column1, column2, ...)] AS (
    SELECT ...            -- the CTE query
),
another_cte AS (          -- more CTEs: comma, no second WITH
    SELECT ... FROM cte_name
)
SELECT ...                -- the ONE main statement that uses them
FROM another_cte;

Example — standalone CTE

WITH team_totals AS (
    SELECT team, SUM(runs) AS total
    FROM players
    GROUP BY team
)
SELECT *
FROM team_totals
WHERE total >= 700;

WITH defines the CTE; the main query reads it by name. Same result as the derived-table version — but reads top to bottom.

Example — multiple CTEs

WITH team_totals AS (
    SELECT team, SUM(runs) AS total
    FROM players
    GROUP BY team
),
team_info AS (
    SELECT team, team_name
    FROM teams
)
SELECT i.team_name, t.total
FROM team_totals AS t
JOIN team_info AS i
    ON t.team = i.team
ORDER BY t.total DESC;

Step by step: define CTE 1 (team_totals) → add CTE 2 (team_info) after a comma → join them in the main query. WITH is written only once.

Example — nested CTEs (a CTE using a CTE)

WITH totals AS (
    SELECT team, SUM(runs) AS total
    FROM players
    GROUP BY team
),
ranked AS (
    SELECT *, RANK() OVER (ORDER BY total DESC) AS rnk
    FROM totals                 -- reads the CTE above
)
SELECT *
FROM ranked
WHERE rnk <= 3;

How it works: totals sums runs per team → ranked reads totals and adds a rank → the main query keeps the top 3. Each CTE can use any CTE defined above it. You cannot filter on rnk in the same SELECT that creates it (window functions run after WHERE), so a CTE step is the standard solution.

Why use CTEs?

Common mistakes

• ❌ Writing WITH twice: WITH a AS (…) WITH b AS (…). ✅ WITH a AS (…), b AS (…).
• ❌ Using a CTE in a second statement after the main query → "Invalid object name". ✅ a CTE lives for one statement only.
• ❌ ORDER BY inside the CTE → not allowed in SQL Server (unless with TOP). ✅ sort in the main query.
• ❌ In SQL Server, a statement before WITH without a semicolon → syntax error. ✅ end the previous statement with ; (many people write ;WITH).
• ❌ Referencing a CTE defined below the current one. ✅ define CTEs in dependency order.

SQL Server vs MySQL / PostgreSQL

Plain CTEs are identical in all three (MySQL needs 8.0+). Only recursive CTEs differ: PostgreSQL and MySQL require WITH RECURSIVE, SQL Server just uses WITH.

Interview tip

Q: CTE vs subquery — which is faster? Usually neither; SQL Server typically produces the same execution plan. Choose a CTE for readability, reuse inside one query, and recursion.