Learn what CTEs (Common Table Expressions) are in SQL, when to use them, and master the WITH clause with visual examples. Includes recursive CTEs.
Jump into the SQLNoir case files and put these tips to work.
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.
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;
The CTE active_suspects filters the suspects table first. The main query then works with that smaller, focused result set.
Nothing fancy. You define a named result set with WITH, then use it in your main query like any other table.
A CTE goes through four stages during query execution. Understanding this lifecycle helps you know exactly what's happening under the hood.
CTE Execution Lifecycle: defined, executed, referenced, then discarded
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.
Let's break down every part of a CTE. Each keyword and clause has a specific job:
suspect_locationss.name, cs.location, cs.crime_datesuspects s JOIN crime_scenes cs ON s.scene_id = cs.idcs.crime_date > '1986-01-01'name, locationsuspect_locationsAnatomy of a CTE: WITH names it, AS defines it, then use it in your main query
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.
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;•Hard to read with nested layers
•Difficult to debug each step
•Must repeat subquery if used elsewhere
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;
•Each step has a clear name
•Easy to debug independently
•Reusable within the same query
Same logic, dramatically different readability. CTEs turn query spaghetti into organized steps.
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.
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;
Three CTEs, each building on the previous. Step 1: find recent crimes. Step 2: find suspects at those scenes. Step 3: find repeat offenders. The main query simply reads the final result.
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;•Nested subquery duplicates logic
•Hard to tell what each part does
•Difficult to modify one step
WITH recent_crimes AS (...), suspects_at_scenes AS (...), repeat_offenders AS (...) SELECT name, scene_count FROM repeat_offenders ORDER BY scene_count DESC;
•Each CTE handles one logical step
•Later CTEs build on earlier ones
•Easy to add or remove steps
Multiple CTEs turn complex analysis into a readable pipeline of named steps
recent_crimes and repeat_offenders are self-documenting. Names like cte1 and temp defeat the purpose.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;
Starting from Victor Malone, this query follows the chain of associates up to 5 levels deep. Each iteration finds the next layer of connections.
Recursive CTE execution: start with anchor rows, keep joining until no new matches
CTEs and subqueries often produce identical results. The choice usually comes down to readability and reuse. Here's a quick reference:
| Aspect | CTE | Subquery |
|---|---|---|
| Readability | Excellent. Named and organized at the top. | Can get messy when nested deeply. |
| Reusability | Reference multiple times in the same query. | Must copy-paste if needed again. |
| Recursion | Supported (WITH RECURSIVE). | Not supported. |
| Performance | Usually identical (optimizer handles both). | Usually identical (optimizer handles both). |
| Best for | Complex, multi-step logic. | Simple, one-off comparisons. |
CTE vs Subquery: choose based on complexity and reuse needs
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.
A body was found at Coral Bay Marina. The investigation requires complex queries with multiple JOINs and filtering across surveillance records, hotel check-ins, and interviews. CTEs would make this investigation much cleaner.
Start InvestigationWITH first_cte AS ( SELECT * FROM suspects ) WITH second_cte AS ( -- ERROR! SELECT * FROM crime_scenes ) SELECT * FROM first_cte JOIN second_cte ON ...;
•Using WITH again for the second CTE
•SQL expects a comma, not a new WITH keyword
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 ...;
•Single WITH keyword at the start
•Comma separates multiple CTE definitions
Only use WITH once. Separate multiple CTEs with commas.
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
The semicolon ends the statement. A CTE only lives within its own statement. Remove the semicolon and put the SELECT right after the CTE definition.
-- 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;
The WHERE c.depth < 10 clause prevents infinite loops. You can also use OPTION (MAXRECURSION 100) in SQL Server as a safety net.
Q1.What keyword starts a CTE definition?
Q2.When should you use a CTE instead of a subquery?
Q3.How long does a CTE exist?
Q4.What type of data is a recursive CTE perfect for?
Ready to put your CTE skills to the test?
SQLNoir's detective cases challenge you to write real SQL queries to solve mysteries. Start with a beginner case and work your way up to advanced investigations that benefit from clean, organized CTEs.
Start Your Investigation →CTE stands for Common Table Expression. It's a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
No. A CTE only exists for the single query where it's defined and is automatically discarded. A temp table persists for the entire session and is stored on disk. CTEs are for readability and organization; temp tables are for reuse across multiple queries.
Usually no. Most database query optimizers treat CTEs and subqueries identically. CTEs improve readability and maintainability, not speed. In some edge cases, CTEs can be slower if the optimizer materializes them when it shouldn't.
Yes. CTEs work with UPDATE, DELETE, INSERT, and MERGE statements, not just SELECT. This is useful for complex data modifications based on calculated values.
A CTE (WITH) is temporary and exists only for one query. A VIEW is a stored object in the database that persists until dropped. Use CTEs for one-off complex queries; use views for reusable query definitions that multiple queries or users need to access.