Subqueries, Views and CTEs

Subqueries, views, and CTEs are the "intermediate SQL" the interviewer uses to gauge depth. "Correlated vs non-correlated subquery" and "view vs materialized view" are common, along with rewriting a nested query as a CTE.

Subqueries

A subquery is a query nested inside another. A non-correlated subquery runs once, independent of the outer query. A correlated subquery references the outer row and runs once per outer row (slower). Subqueries appear in WHERE, FROM (derived table), or SELECT.

Views

A view is a virtual table defined by a stored query — it stores no data, just the definition, and runs fresh each time. A materialized view stores the result physically and must be refreshed — faster reads, but potentially stale.

CTEs (Common Table Expressions)

A CTE (WITH name AS (...)) is a named temporary result set that exists for one query. It improves readability over nested subqueries and can be recursive (for hierarchies/trees).

Feature	Stores data	Scope
View	no	persistent object
Materialized view	yes	persistent + refresh
CTE	no	single query

Exam Tricks & Tips

• 🎯 A correlated subquery references the outer query and runs per outer row; a non-correlated one runs once — the key performance distinction.
• 🎯 A view stores no data (just the query); a materialized view stores results and needs refreshing (faster reads, possibly stale).
• 🎯 A CTE (WITH) improves readability and enables recursion for tree/hierarchy traversal.
• 🎯 Views can simplify complex queries and provide a security layer (expose only some columns).
• 🎯 A view is updatable only if it's simple (no aggregates/joins/DISTINCT/GROUP BY).
• 🎯 A subquery returning multiple rows needs IN/ANY/ALL, not = — using = errors.
• ❌ Common mistake: assuming a normal view caches data — it re-executes every access; only a materialized view stores results.

Expected pattern

"Correlated vs non-correlated subquery", "view vs materialized view", "what is a CTE / recursive CTE?", "are views updatable?", and rewriting a subquery as a CTE.

Quick recap

Subquery = nested query; correlated ones reference the outer row and run per row (slower). View = virtual table storing only the definition; materialized view stores results (refresh needed). CTE (WITH) = named single-query temp result, readable and recursion-capable. Multi-row subqueries need IN/ANY/ALL.

Cover the answer, recall, then check. 12 cards on subqueries, views and CTEs.

Q1. What is a subquery?
A1. A query nested inside another query, usable in WHERE, FROM (derived table), or SELECT.

Q2. Difference between correlated and non-correlated subqueries?
A2. A correlated subquery references the outer query and runs once per outer row; a non-correlated one runs once, independently.

Q3. Which is generally slower and why?
A3. The correlated subquery — it re-executes for every row of the outer query.

Q4. What is a view?
A4. A virtual table defined by a stored query; it holds no data and re-runs its query on each access.

Q5. What is a materialized view?
A5. A view whose query results are physically stored on disk; faster to read but must be refreshed and can be stale.

Q6. What is a CTE?
A6. A Common Table Expression (WITH name AS (...)) — a named temporary result set scoped to a single query.

Q7. What advantage does a recursive CTE provide?
A7. It can traverse hierarchical/tree data (e.g. org charts, bill-of-materials) by referencing itself.

Q8. When is a view updatable?
A8. When it's simple — no aggregates, joins, DISTINCT, or GROUP BY that prevent mapping updates back to base rows.

Q9. What operators are needed for a subquery returning multiple rows?
A9. IN, ANY, or ALL — not =, which expects a single value.

Q10. Does a normal (non-materialized) view cache its data?
A10. No — it stores only the query definition and re-executes it each time it's queried.

Q11. Name one security benefit of views.
A11. They can expose only selected columns/rows, hiding sensitive data from users who query the view.

Q12. How does a CTE improve on nested subqueries?
A12. It gives the intermediate result a readable name and can be referenced multiple times, improving clarity.

Complex questions often need a query inside a query, or a reusable named result. Subqueries, views and CTEs are the three tools for composing SQL — and knowing when each fits (and the correlated-subquery performance trap) marks a competent SQL author.

Core idea: a subquery is a query nested inside another (in WHERE, FROM, or SELECT). A view is a saved named query you can query like a table. A CTE (Common Table Expression, WITH) is a temporary named result defined for a single statement — improving readability and enabling recursion.

How it works

Beginner — subqueries

-- rows where salary beats the company average
SELECT name FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);   -- scalar subquery

A scalar subquery returns one value; an IN subquery returns a column of values; an EXISTS subquery tests for any matching row.

Intermediate — correlated vs non-correlated

• Non-correlated: the inner query is independent — it runs once, its result feeds the outer query.
• Correlated: the inner query references the outer row, so it re-executes once per outer row — potentially O(n²) and slow.

-- correlated: for EACH employee, compare to their OWN department's average
SELECT name FROM Employee e
WHERE salary > (SELECT AVG(salary) FROM Employee
                WHERE dept_id = e.dept_id);   -- references outer e -> correlated

Advanced — views and CTEs

A view persists a query definition in the schema:

CREATE VIEW HighEarners AS
SELECT name, dept_id FROM Employee WHERE salary > 100000;
SELECT * FROM HighEarners WHERE dept_id = 3;   -- query it like a table

Views give abstraction (hide complexity), security (expose only some columns), and reusability; most are read-only for querying, though simple ones can be updatable. A CTE is scoped to one statement and reads top-to-bottom, which untangles nested subqueries — and uniquely supports recursion (hierarchies, graphs):

WITH RECURSIVE Chain AS (
    SELECT id, manager_id FROM Employee WHERE id = 7        -- anchor
    UNION ALL
    SELECT e.id, e.manager_id
    FROM Employee e JOIN Chain c ON e.manager_id = c.id     -- recursive step
)
SELECT * FROM Chain;                                        -- whole reporting tree

Worked example — CTE vs nested subquery readability

Find departments whose average salary exceeds the overall company average.

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS a FROM Employee GROUP BY dept_id
)
SELECT dept_id, a FROM dept_avg
WHERE a > (SELECT AVG(salary) FROM Employee);

The CTE dept_avg names the per-department averages once, so the final query reads plainly. Writing the same logic as a subquery in the FROM clause works identically but nests the grouping inside parentheses, which is harder to follow and can't be referenced twice. CTEs shine when a derived table is used multiple times or the logic is layered.

Interview & exam relevance

Common asks: "difference between a subquery and a join" (often interchangeable; joins usually faster), "correlated vs non-correlated subquery and its performance", "view vs CTE" (persistent schema object vs single-statement temporary), and "how do you query a hierarchy" (recursive CTE). EXISTS vs IN (and NULL behaviour of NOT IN) is a frequent trap.

Tricks & gotchas

• A correlated subquery re-runs per outer row — rewrite as a join or CTE when performance matters.
• NOT IN with a subquery that yields any NULL returns no rows (the comparison becomes unknown) — prefer NOT EXISTS.
• A view is stored in the schema and reusable across queries; a CTE exists only for the statement that defines it.
• Mnemonic: "Subquery = nested; View = saved; CTE = named-for-now."

Worked Example

Problem: From Employees(id, name, dept_id, salary), write three equivalent-in-spirit solutions to "list employees earning more than their department's average salary": (a) a correlated subquery, (b) a CTE, and explain how a view would package it. Contrast a correlated vs non-correlated subquery.

Solution:
(a) Correlated subquery — the inner query references the outer row, so it re-evaluates per employee:

SELECT e.name, e.salary, e.dept_id
FROM Employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM Employees e2
    WHERE e2.dept_id = e.dept_id   -- correlation to outer e
);

For each employee e, the subquery computes the average salary of e's own department (e2.dept_id = e.dept_id), and keeps e if their salary beats it. Because it depends on the outer row, it logically runs once per row (a correlated subquery). A non-correlated subquery, by contrast, is independent of the outer query and runs once (e.g. WHERE salary > (SELECT AVG(salary) FROM Employees) — the whole-company average).

(b) CTE (Common Table Expression) — name a temporary result with WITH, then join to it. This computes each department's average ONCE, which is usually clearer and lets the planner avoid per-row recomputation:

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM Employees
    GROUP BY dept_id
)
SELECT e.name, e.salary
FROM Employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

The CTE dept_avg is defined once and referenced like a table; it improves readability and can be self-referential for recursion.

View — a view stores the query definition (not the data) under a name, so callers query it like a table:

CREATE VIEW above_avg_earners AS
WITH dept_avg AS (SELECT dept_id, AVG(salary) avg_sal FROM Employees GROUP BY dept_id)
SELECT e.id, e.name, e.salary
FROM Employees e JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;
-- then simply: SELECT * FROM above_avg_earners;

The view re-runs its definition each time it is queried (always current), encapsulating the logic and simplifying access control.

Answer: All three return employees whose salary exceeds their department average. The correlated subquery re-evaluates per outer row (vs a non-correlated one that runs once); the CTE names the per-department averages once for clarity; a view persists that query definition so callers select from it like a table.