Subqueries and CTEs

Analogy categoryAnalogies

Scalar and Correlated Subqueries

A scalar subquery returns exactly one value and can be used anywhere a single expression is expected, such as SELECT ProductName, UnitPrice, (SELECT AVG(UnitPrice) FROM Product) AS AvgPrice FROM Product. A correlated subquery references a column from the outer query and is re-evaluated once per outer row — for example, WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID) checks, for each customer row, whether any matching order exists, which is very different from a non-correlated subquery that runs once and produces a fixed set of values.

🏏

Cricket analogy: A scalar subquery computing (SELECT AVG(Runs) FROM Innings) once produces the tournament's overall batting average to compare every player against, while a correlated subquery checking WHERE EXISTS (SELECT 1 FROM Innings i WHERE i.PlayerID = p.PlayerID AND i.Runs > 100) re-runs per player to test if each one has ever scored a century.

sql

-- Correlated subquery: customers with at least one order over $1000
SELECT c.CustomerID, c.CustomerName
FROM Sales.Customer c
WHERE EXISTS (
    SELECT 1
    FROM Sales.Orders o
    WHERE o.CustomerID = c.CustomerID
      AND o.TotalAmount > 1000
);

EXISTS typically outperforms IN when checking for the presence of related rows, because SQL Server can stop scanning as soon as one matching row is found (short-circuit), and EXISTS handles NULL values in the subquery's result more predictably than NOT IN.

NOT IN with a subquery that can return NULL is a classic trap: if even one row in the subquery result has a NULL value, NOT IN returns zero rows for the entire outer query, because comparing anything to NULL yields UNKNOWN. Use NOT EXISTS instead to avoid this.

Common Table Expressions (CTEs)

A CTE, defined with WITH CteName AS (...), is a named temporary result set that exists only for the duration of the single statement that follows it, making complex logic readable by breaking it into named, sequential steps instead of deeply nested subqueries. Multiple CTEs can be chained in one WITH clause separated by commas, and a later CTE can reference an earlier one, but a CTE cannot reference itself except in the special case of a recursive CTE.

🏏

Cricket analogy: A CTE named HighScorers AS (SELECT ... WHERE Runs > 50) lets a later step simply SELECT * FROM HighScorers, reading like a named intermediate scoreboard rather than a deeply nested subquery buried inside another query.

sql

WITH RegionalSales AS (
    SELECT Region, SUM(TotalAmount) AS TotalRevenue
    FROM Sales.Orders
    GROUP BY Region
),
TopRegions AS (
    SELECT Region, TotalRevenue
    FROM RegionalSales
    WHERE TotalRevenue > 500000
)
SELECT * FROM TopRegions ORDER BY TotalRevenue DESC;

Recursive CTEs

A recursive CTE has an anchor member (the base case, executed once) UNION ALL a recursive member that references the CTE name itself, repeating until the recursive member returns no more rows — this is the standard T-SQL pattern for walking hierarchies like an employee-manager org chart or a bill-of-materials tree. Without a terminating condition, a recursive CTE can loop indefinitely, so SQL Server enforces a default MAXRECURSION of 100, which can be raised or disabled with the OPTION (MAXRECURSION n) query hint.

🏏

Cricket analogy: A recursive CTE walking a bowling-partnership tree starts with an anchor of the opening bowlers, then recursively adds each subsequent bowling change, similar to tracing a Test match's full bowling rotation over five days without hardcoding every over.

Scalar subqueries return exactly one value and can be used as an expression.
Correlated subqueries reference the outer query and re-evaluate once per outer row.
EXISTS is generally preferred over IN for existence checks; NOT IN with NULLs is a common bug source.
CTEs (WITH ... AS) create named, readable intermediate result sets scoped to one statement.
Multiple CTEs can chain, with later CTEs referencing earlier ones.
Recursive CTEs need an anchor member, UNION ALL, and a recursive member referencing the CTE itself.
SQL Server enforces MAXRECURSION 100 by default to prevent infinite recursive CTE loops.

Practice what you learned

1. What defines a correlated subquery?

2. Why is NOT IN risky when the subquery result can contain NULL?

3. What is the scope of a CTE defined with WITH CteName AS (...)?

4. What two parts must a recursive CTE contain?

5. What is the default MAXRECURSION limit in SQL Server for a recursive CTE?

Was this page helpful?

Aggregations and Grouping

Window Functions

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Snowflake Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Topics covered

#Programming#SQLServerTSQLStudyNotes#SubqueriesAndCTEs#Subqueries#CTEs#Scalar#Correlated#StudyNotes#SkillVeris#ExamPrep

Scalar and Correlated Subqueries

Common Table Expressions (CTEs)

Recursive CTEs

Practice what you learned

You May Also Like

Related Reading

Related Study Notes in Programming

Topics covered