SQL Views & CTEs (WITH Clause) Explained

By now your queries have grown teeth — joins inside subqueries inside aggregations. Imagine emailing a 30-line monster to a teammate with "just run this whenever you need the sales summary". There's a better way: give the query a name. Name it permanently and you have a view; name it for just one statement and you have a CTE. This lesson is about both — plus the recursive trick that lets SQL walk an entire org chart.

Views — a saved query wearing a table costume

CREATE VIEW high_earners AS
SELECT employee_id, name, salary
FROM employees
WHERE salary > 70000;

-- now anyone can just:
SELECT * FROM high_earners;

A view stores only the query, not the data — query the view and the engine runs the underlying SELECT against live tables, so results are always fresh. Two superpowers come free: simplification (a monster join hides behind one name, used by everyone identically) and security — notice high_earners exposes no department column, and could equally hide salary from juniors or restrict rows to one region. Grant access to the view, not the table, and the hidden columns simply don't exist for that user.

Updatable views — when can you write through one?

Can you UPDATE high_earners SET ...? Sometimes. The test is whether the engine can map your change back to exactly one row of one base table. A simple single-table, row-filtering view — yes. But add any of: aggregates, GROUP BY, DISTINCT, UNION, or most joins — and the mapping breaks. (Which underlying row would updating an average even mean?) Bonus interview point: WITH CHECK OPTION stops you updating a row out of the view — e.g. setting a high-earner's salary to 50000, which would make it vanish from the view it was edited through.

Materialized views — trading freshness for speed

A regular view re-runs its query every time — always fresh, repeatedly expensive. A materialized view flips the trade: it stores the result like a real table, so reads are instant, but the snapshot goes stale until you refresh it. Perfect for dashboard aggregations where "as of last hour" is fine. One MySQL honesty point: MySQL has no native materialized views — teams emulate them with summary tables refreshed by jobs; PostgreSQL and Oracle support them natively. View = always fresh, pay per read; materialized = instant reads, pay in staleness.

CTEs — naming steps inside one query

A Common Table Expression is a temporary named result that lives for exactly one statement — SQL's version of naming your intermediate steps:

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

Engineering | 84000.00

Read it like a recipe: first compute department averages, then keep the big ones. Chain more steps with commas — WITH step1 AS (…), step2 AS (…) SELECT … — each step allowed to use the previous ones. This top-to-bottom readability is why analysts write almost everything with CTEs.

CTE vs subquery vs temp table vs view — pick your tool

Recursive CTEs — SQL climbs the org chart

Back in lesson 2, a self join found each employee's direct manager — one level. But "everyone who reports to Asha, directly or indirectly" needs to walk the whole tree, however deep. A recursive CTE has exactly two parts:

WITH RECURSIVE subordinates AS (
  -- 1. Anchor: runs once — the starting rows
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id = 1

  UNION ALL

  -- 2. Recursive member: joins back onto what's found so far
  SELECT e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates ORDER BY level;

The anchor seeds the result (Asha's direct reports, level 1); the recursive member runs again and again — each pass descending one level — until it produces no new rows. That's the built-in stopping rule; forget a terminating condition (say, a date series with no upper bound) and the engine aborts at its recursion limit (MySQL's cte_max_recursion_depth, default 1000). Hierarchies, category trees, date-series generation — one pattern rules them all, and you'll write two of them in the practice.

Common mistakes

• Thinking a view stores data — it stores a query (that's the materialized view's job).
• Trying to UPDATE through an aggregated/grouped view — the row mapping doesn't exist.
• Creating permanent views for one-off analyses — that's CTE territory; views are shared, long-lived interfaces.
• Forgetting the RECURSIVE keyword in MySQL/PostgreSQL recursive CTEs (SQL Server manages without it).
• Writing a recursive member whose join can't exhaust — infinite recursion until the depth limit errors out.

Quick recap

Practice Zone — PYQs from real selection rounds

Q1Viewseasy

What does a regular (non-materialized) view actually store?

Asked in

AOnly its SELECT query — the data is fetched fresh on every useBA full copy of the result rowsCThe first 1000 rows as a cacheDPointers to the physical rows

Q2Materialized viewsmedium

What's the core trade-off of a materialized view versus a regular view?

Asked in

AFast reads from stored results — but the data can be stale until refreshedBIt's always slower but always freshCIt can't be queried directlyDIt only works on a single table

Q3Updatable viewsmedium

Which of these views is NOT updatable (no INSERT/UPDATE/DELETE through it)?

Asked in

ACREATE VIEW v AS SELECT department, AVG(salary) FROM employees GROUP BY department;BCREATE VIEW v AS SELECT emp_id, name FROM employees;CCREATE VIEW v AS SELECT * FROM employees WHERE salary > 50000;DThey're all updatable

Q4CTE vs the alternativesmedium

You need the same computed result twice within one query, readably. Best tool?

Asked in

AA CTE — name it once with WITH, reference it twiceBCopy-paste the subquery twiceCA permanent view for this one queryDA temporary table, always

Q5Recursive CTEshard

A recursive CTE has two parts joined by UNION ALL. What are they, and what happens with no termination condition?

Asked in

AAnchor member + recursive member; without termination it loops until the recursion limit errors outBInner and outer query; it silently returns nothingCSELECT and WHERE; it returns one rowDTwo identical copies; termination is automatic

FAQ

Final lesson: the four families every SQL command belongs to — Lesson 12: DDL, DML, DCL & TCL →