Subqueries, CTEs & Recursive CTEs

Subqueries and Common Table Expressions (CTEs) let you decompose complex problems into named, readable steps. Recursive CTEs handle hierarchical and graph traversal directly in SQL.

Subquery Forms

Scalar Subquery

Returns exactly one row, one column:

SELECT  e.employee_id,
        e.first_name,
        e.salary,
        (SELECT AVG(salary) FROM employees) AS company_avg
FROM    employees e;

Multi-Row Subquery (IN)

SELECT first_name, last_name
FROM   employees
WHERE  department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);

Correlated Subquery

References a column from the outer query — runs once per outer row:

-- Employees earning above their department's average
SELECT  e.employee_id, e.first_name, e.salary, e.department_id
FROM    employees e
WHERE   e.salary > (
    SELECT AVG(salary)
    FROM   employees e2
    WHERE  e2.department_id = e.department_id
);

EXISTS / NOT EXISTS

EXISTS returns TRUE as soon as the subquery yields any row — ideal for "does any matching row exist?" tests, and immune to NULL pitfalls that plague NOT IN:

-- Departments that have employees
SELECT department_id, department_name
FROM   departments d
WHERE  EXISTS (
    SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);

-- Customers who have NEVER placed an order
SELECT c.customer_id, c.customer_name
FROM   customers c
WHERE  NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Derived Tables

A subquery in the FROM clause:

SELECT dept, top_salary
FROM (
    SELECT department_id AS dept, MAX(salary) AS top_salary
    FROM   employees
    GROUP BY department_id
) AS dept_top
WHERE top_salary > 10000;

Common Table Expressions (CTEs)

A CTE is a named, temporary result set defined with WITH ... AS (...). They're a more readable alternative to nested derived tables and are mandatory for recursive queries.

WITH dept_stats AS (
    SELECT  department_id,
            COUNT(*)   AS staff,
            AVG(salary) AS avg_sal
    FROM    employees
    GROUP BY department_id
),
big_depts AS (
    SELECT * FROM dept_stats WHERE staff >= 5
)
SELECT  d.department_name, b.staff, b.avg_sal
FROM    big_depts b
JOIN    departments d ON d.department_id = b.department_id
ORDER BY b.avg_sal DESC;

CTEs are scoped to the single statement that follows. Define multiple CTEs by separating them with commas.

Recursive CTEs

A recursive CTE has two parts: an anchor member (the seed rows) and a recursive member (which references the CTE itself), connected with UNION ALL.

Org-Chart Walk

;WITH org_chart AS (
    -- Anchor: top-level employees (no manager)
    SELECT  employee_id,
            first_name + ' ' + last_name AS name,
            manager_id,
            0 AS level
    FROM    employees
    WHERE   manager_id IS NULL

    UNION ALL

    -- Recursive: join managers to their direct reports
    SELECT  e.employee_id,
            REPLICATE('  ', oc.level + 1) + e.first_name + ' ' + e.last_name,
            e.manager_id,
            oc.level + 1
    FROM    employees e
    JOIN    org_chart oc ON oc.employee_id = e.manager_id
)
SELECT level, name FROM org_chart ORDER BY level, name;

Number Series Generator

;WITH numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers
OPTION (MAXRECURSION 200);     -- override default of 100

The default MAXRECURSION ceiling is 100. Use OPTION (MAXRECURSION 0) for unlimited (use sparingly), or a specific number for safety.

Date Spine

;WITH dates AS (
    SELECT CAST('2024-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(day, 1, d) FROM dates WHERE d < '2024-12-31'
)
SELECT d FROM dates
OPTION (MAXRECURSION 400);

Anti-Joins With NOT EXISTS

Recurring pattern — find rows in A with no match in B:

-- Departments with no employees
SELECT  d.department_id, d.department_name
FROM    departments d
WHERE   NOT EXISTS (
    SELECT 1 FROM employees e
    WHERE  e.department_id = d.department_id
);

Best Practices

• Use a CTE when the same subquery appears more than once or you need to chain transformations — readability beats cleverness.
• Use EXISTS/NOT EXISTS over IN/NOT IN when the right side may contain NULLs.
• Always cap recursion with MAXRECURSION unless you have explicit termination — runaway recursion blocks your session.
• The optimiser does not always materialise a CTE; if a CTE is referenced multiple times and is expensive, a temp table may perform better.

Summary

• Subqueries come in scalar, multi-row (IN), correlated, and EXISTS flavours.
• CTEs (WITH ... AS) replace nested derived tables and improve readability.
• Recursive CTEs walk hierarchies and generate sequences using UNION ALL between anchor and recursive members.
• Default MAXRECURSION is 100; override with OPTION (MAXRECURSION n).