Subqueries & CTEs

A query inside a query; a CTE (WITH …) names it for readable, reusable, recursive steps.

SQL · Intermediate · ~4 min

In plain English

Name an intermediate result and use it like a table. It turns one unreadable nested query into a sequence of readable steps.

Why it's worth your time

It's how real analytical SQL stays maintainable, and CTEs are what interviewers hope you'll reach for.

If you remember three things

WITH name AS (...) defines a named step
CTEs read top-to-bottom; nested subqueries read inside-out
Recursive CTEs walk hierarchies and graphs

Overview

A subquery is a SELECT nested inside another statement — in WHERE (IN (SELECT …)), in FROM as a derived table, or correlated to the outer row so it runs per row. A common table expression, written WITH name AS (…), lifts that logic out front and names it, so the main query reads top-down. CTEs can be referenced multiple times and can recurse to walk hierarchies.

In an interview

Subqueries are queries within queries: they filter (WHERE … IN), derive tables (FROM), or correlate to each outer row. CTEs (WITH name AS …) give a subquery a name up front, making complex SQL readable, reusable, and — with WITH RECURSIVE — able to traverse trees and graphs.

Production defaults

Prefer CTEs: for readability. Multiple chained CTEs beat one deeply nested query every time
Correlated subqueries: run per row — usually rewrite as a join or a window function
Materialization: engines differ on whether a CTE is materialized. Check EXPLAIN when a query is slow

What breaks

CTE version is slower than the nested one — The engine materialized it instead of inlining. Some databases offer a hint; otherwise restructure.
Recursive CTE never terminates — Missing base case or a cycle in the data. Add a depth counter and cap it.

Watch it explained

Advanced SQL Tutorial | CTE (Common Table Expression) — Alex The Analyst, 3:43