Subqueries and CTEs¶

Subqueries and CTEs (Common Table Expressions) are how you break complex SQL problems into manageable steps. Interviewers test these to see whether you can decompose a multi-step analytical question into readable, correct SQL — and whether you know when each tool is appropriate.

Q1: What is a subquery, and what are the different places you can use one?¶

Show answer

A subquery is a query nested inside another query. The outer query uses the inner query's result as if it were a table, a value, or a set. Subqueries can appear in three positions:

In the FROM clause (inline view / derived table) — the subquery produces a temporary table:

SELECT dept_name, avg_salary
FROM (
    SELECT department AS dept_name, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) dept_averages
WHERE avg_salary > 70000;

In the WHERE clause — used for filtering:

-- Employees who earn more than the company-wide average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

In the SELECT clause (scalar subquery) — produces one value per row:

SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Interview tip: subqueries in SELECT are evaluated once per output row if correlated, or once total if uncorrelated. For large result sets, correlated scalar subqueries can be slow — a window function is usually the better choice.

Q2: What is the difference between a correlated and an uncorrelated subquery?¶

Show answer

The key distinction is whether the inner query references a column from the outer query.

Uncorrelated subquery — the inner query is independent. It runs once, produces a result, and the outer query uses that result. Efficient.

-- Inner query runs once; the result is used for every outer row comparison
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Correlated subquery — the inner query references a column from the outer query. It runs once per row of the outer query. Can be slow on large tables.

-- For each employee, count how many colleagues in the same department earn more
SELECT
    e.name,
    e.salary,
    (
        SELECT COUNT(*)
        FROM employees e2
        WHERE e2.department = e.department    -- references outer query's 'e'
          AND e2.salary > e.salary
    ) AS higher_paid_colleagues
FROM employees e;

The correlated subquery executes once for every row in employees — if there are 10,000 employees, the inner query runs 10,000 times. In this case, a window function with RANK() would compute the same result in one pass.

Knowing the performance difference between correlated and uncorrelated subqueries is what interviewers probe for. "Can you rewrite this correlated subquery more efficiently?" is a common follow-up.

Q3: What is a scalar subquery in SELECT, and when is it appropriate?¶

Show answer

A scalar subquery in the SELECT clause returns exactly one column and one row — a single value that is appended to each output row.

SELECT
    product_id,
    product_name,
    price,
    (SELECT MAX(price) FROM products)          AS max_price,
    price / (SELECT MAX(price) FROM products)  AS relative_price
FROM products;

If the subquery returns more than one row, the database raises an error. If it returns zero rows, the result is NULL.

When is it appropriate: - The value is a global constant (like a company-wide average or a reference date) computed once - The number of rows in the outer query is small - Readability is more important than performance

When to prefer a window function instead: - You need a value computed per partition (e.g., department average, not company average) - The outer query has many rows — a correlated scalar subquery runs once per row

-- Scalar subquery approach (runs subquery once per row if uncorrelated)
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS avg_sal FROM employees;

-- Window function approach (single pass, better for large tables)
SELECT name, salary, AVG(salary) OVER () AS avg_sal FROM employees;

Q4: What is the difference between EXISTS and IN? Which is safer?¶

Show answer

Both EXISTS and IN can check for membership in a set, but they behave differently with NULLs and at scale.

IN — matches the outer column against a list or subquery result:

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM premium_customers);

IN materialises the full subquery result into a set, then checks each outer row against it. When the subquery returns many rows, this can be a large in-memory set.

EXISTS — checks whether the subquery returns at least one row. It short-circuits as soon as a match is found:

SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM premium_customers pc WHERE pc.id = o.customer_id
);

Key differences:

NULL handling: IN returns NULL (not TRUE) when comparing against a list that contains NULL, which can silently suppress rows. EXISTS is not affected by NULLs — it only checks whether a row exists.
Performance: EXISTS stops searching as soon as it finds one match (short-circuit). IN typically builds the full set. On large subquery results, EXISTS is often faster — though modern query optimisers often rewrite them identically.
Readability: EXISTS is more explicit about intent — "does at least one row satisfy this condition?" IN reads more like a value membership check.

Prefer EXISTS for correlated membership checks, especially when the subquery can produce NULLs. Use IN for small, static lists or when the subquery is guaranteed NOT NULL.

Q5: What is the NOT IN / NULL trap and how do you avoid it?¶

Show answer

NOT IN with a subquery is one of the most dangerous patterns in SQL. If the subquery returns even one NULL, NOT IN returns zero rows — silently, with no error.

The reason: x NOT IN (..., NULL) is equivalent to x != val1 AND x != val2 AND ... AND x != NULL. Since x != NULL evaluates to NULL (not TRUE), the entire AND chain becomes NULL, and no rows pass the filter.

-- Dangerous: if orders.customer_id has any NULLs, this returns zero rows
SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe: NOT EXISTS is not affected by NULLs
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Also safe: LEFT JOIN anti-join
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

The fix if you must use NOT IN: filter out NULLs in the subquery explicitly.

WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)

This is one of the most-cited SQL gotchas in interviews. Interviewers often present a NOT IN query and ask "what could go wrong?" The answer is always: NULLs in the subquery.

Q6: What is a Common Table Expression (CTE) and why is it preferable to a subquery?¶

Show answer

A CTE (Common Table Expression) is a named, temporary result set defined at the top of a query using the WITH keyword. It exists only for the duration of the query.

WITH dept_averages AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_averages d ON e.department = d.department
WHERE e.salary > d.avg_salary;

Why CTEs are preferred over nested subqueries:

Readability — each logical step has a name and is defined top-to-bottom. A nested subquery requires reading inside-out.
Reusability — a CTE can be referenced multiple times in the same query. A subquery must be repeated or wrapped in another layer.
Debuggability — you can run each CTE independently (by selecting just the WITH block) to verify intermediate results.
Maintainability — when the logic changes, you edit one named block instead of hunting through nested parentheses.

In PostgreSQL, CTEs are not automatically "inlined" into the main query (prior to PostgreSQL 12, CTEs were optimisation fences). Since PostgreSQL 12, the planner can inline CTEs unless you use WITH ... AS MATERIALIZED. In most databases, treat CTEs as a readability tool; let the planner handle execution strategy.

Q7: How do you chain multiple CTEs together?¶

Show answer

Multiple CTEs are separated by commas after the initial WITH. Later CTEs can reference earlier ones — building a multi-step pipeline within a single query.

WITH
-- Step 1: aggregate monthly revenue per product
monthly_revenue AS (
    SELECT
        product_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY product_id, DATE_TRUNC('month', order_date)
),

-- Step 2: compute month-over-month growth using previous CTE
monthly_growth AS (
    SELECT
        product_id,
        month,
        revenue,
        LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS prev_revenue,
        revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS revenue_delta
    FROM monthly_revenue
),

-- Step 3: flag products with declining revenue for 3+ consecutive months
declining_products AS (
    SELECT product_id
    FROM monthly_growth
    WHERE revenue_delta < 0
    GROUP BY product_id
    HAVING COUNT(*) >= 3
)

-- Final output
SELECT p.product_name, p.category
FROM products p
WHERE p.product_id IN (SELECT product_id FROM declining_products);

This is far more readable than the equivalent nest of subqueries. Each step has a clear name, and you can isolate any CTE for debugging.

Interview tip: when a problem has 3+ logical steps, reach for chained CTEs immediately. It shows you write production-quality, maintainable SQL.

Q8: What is a recursive CTE and what problems does it solve?¶

Show answer

A recursive CTE references itself, enabling queries that iterate over hierarchical or sequential data. Common uses: org hierarchies, category trees, date sequence generation, graph traversal.

Structure:

WITH RECURSIVE cte_name AS (
    -- Anchor: the starting point (non-recursive)
    SELECT ...

    UNION ALL

    -- Recursive part: references cte_name itself
    SELECT ... FROM cte_name WHERE <termination condition>
)
SELECT * FROM cte_name;

Example 1: Org hierarchy — find all reports under a given manager at any depth:

WITH RECURSIVE org_tree AS (
    -- Anchor: start with the root manager (id = 5)
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 5

    UNION ALL

    -- Recursive: add direct reports of each already-found employee
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

Example 2: Date sequence — generate every date in a range without a calendar table:

WITH RECURSIVE date_series AS (
    SELECT '2024-01-01'::DATE AS d

    UNION ALL

    SELECT d + INTERVAL '1 day'
    FROM date_series
    WHERE d < '2024-01-31'
)
SELECT d FROM date_series;

The recursive part runs until the WHERE condition is false. Always include a termination condition — without it, the query loops indefinitely (most databases have a maximum recursion depth limit that will eventually error).

Q9: When should you use a CTE vs a subquery vs a temporary table?¶

Show answer

All three can store an intermediate result set, but they suit different situations.

Subquery — use when the intermediate result is small, used in only one place, and the query is short. Inline subqueries avoid naming overhead.

SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);

CTE — use when: - The intermediate result is referenced more than once - The query has 3+ logical steps (readability payoff is significant) - You want named, self-documenting steps - The dataset fits in memory during query execution

WITH avg_order AS (SELECT AVG(amount) AS avg FROM orders)
SELECT o.*, avg_order.avg
FROM orders o, avg_order
WHERE o.amount > avg_order.avg;

Temporary table — use when: - The intermediate result is large and reused across multiple subsequent queries - You need an index on the intermediate result for performance - You are building a multi-query ETL pipeline (not a single query) - The database materialises CTEs as full scans and the same CTE is referenced many times

CREATE TEMP TABLE high_value_customers AS
SELECT customer_id, SUM(amount) AS ltv
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000;

CREATE INDEX ON high_value_customers (customer_id);

-- Now use the temp table in multiple subsequent queries
SELECT * FROM high_value_customers JOIN ...;
SELECT * FROM high_value_customers JOIN ...;

Rule of thumb: start with CTEs for readability; switch to temp tables when performance or reuse across queries demands materialisation.

Q10: How do you rewrite a deeply nested subquery as a CTE?¶

Show answer

Deeply nested subqueries are a maintenance risk — they must be read inside-out and are difficult to debug. CTEs let you flatten them into sequential steps.

Before — nested subquery (hard to read):

SELECT product_name
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_items
    WHERE order_id IN (
        SELECT order_id
        FROM orders
        WHERE customer_id IN (
            SELECT id FROM customers WHERE country = 'Germany'
        )
    )
);

After — chained CTEs (readable, debuggable):

WITH
german_customers AS (
    SELECT id FROM customers WHERE country = 'Germany'
),
german_orders AS (
    SELECT order_id FROM orders
    WHERE customer_id IN (SELECT id FROM german_customers)
),
ordered_products AS (
    SELECT DISTINCT product_id FROM order_items
    WHERE order_id IN (SELECT order_id FROM german_orders)
)
SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM ordered_products);

The logic is identical; the CTE version makes each step named and independently testable. You can add a SELECT * to any CTE to validate the intermediate data.

When refactoring: start from the innermost subquery, extract it as the first CTE, then work outward level by level.

Q11: How does EXISTS behave differently from IN on large datasets?¶

Show answer

On large datasets, EXISTS is often faster than IN because of short-circuit evaluation — EXISTS stops searching as soon as it finds the first matching row. IN evaluates the full subquery, builds the complete set, and then checks membership.

Modern query optimisers (PostgreSQL, SQL Server, Oracle) often rewrite IN as a semi-join, making them equivalent in execution. But in older databases or complex queries, the difference can be significant.

-- IN: subquery must produce the full list of premium customer IDs
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'premium');

-- EXISTS: stops at first match per order row
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c
    WHERE c.id = o.customer_id AND c.tier = 'premium'
);

When EXISTS is clearly better: - The subquery returns a very large result set and you only need to know if at least one row exists - The inner table has an index on the join column — EXISTS can use it immediately and stop

When IN is appropriate: - The list is small and static: WHERE status IN ('active', 'pending', 'review') - The subquery is uncorrelated and the result is reused by the planner

For interview answers: acknowledge that modern optimisers often make them equivalent, but explain the conceptual difference and the NULL gotcha with NOT IN.

Q12: How do you use a CTE to simplify a "find the second highest value" query?¶

Show answer

"Find the Nth highest value" is a classic interview question. Several approaches exist; the CTE + DENSE_RANK version is the most robust.

Approach 1: DENSE_RANK in a CTE (handles ties correctly)

WITH ranked_salaries AS (
    SELECT
        employee_id,
        name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS sal_rank
    FROM employees
)
SELECT employee_id, name, salary
FROM ranked_salaries
WHERE sal_rank = 2;

DENSE_RANK assigns rank 2 to all employees with the second-highest salary (even if multiple employees share it). ROW_NUMBER would arbitrarily give rank 2 to only one of them.

Approach 2: Subquery with LIMIT/OFFSET (fragile with ties)

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Returns the second-distinct salary value but requires a second query to find employees at that salary.

Approach 3: Nested subquery (not scalable to Nth)

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Works for N=2 only; does not generalise.

The DENSE_RANK CTE approach generalises to any N — change WHERE sal_rank = 2 to any target rank. It is the expected production answer.