Views in SQL

A view is a virtual table defined by a SQL query over one or more base tables (or other views). Syntactically: ```sql CREATE VIEW DeptSummary AS SELECT dept_id, AVG(salary) AS avg_salary, COUNT() AS headcount FROM Employee GROUP BY dept_id; ``` Once created, `DeptSummary` can appear in `FROM` clauses, joins, and subqueries exactly like a table: `SELECT FROM DeptSummary WHERE headcount > 10`. Crucially, the database does not compute and store `DeptSummary` at creation time (unless it is explicitly a materialized view). Instead, the view's defining query is retained as metadata in the system catalog. When a query references the view, the query processor performs view expansion: it textually (or, more precisely, at the level of the internal query representation — the relational-algebra or parse-tree form) substitutes the view's defining query for every occurrence of the view name, then optimizes the resulting composite query as a whole. This distinguishes two categories sharply. A virtual view (the default in standard SQL, sometimes just called "a view") has zero storage cost and is always perfectly up to date, because it is recomputed — conceptually, though not always literally — every time it is queried. A materialized view (`CREATE MATERIALIZED VIEW` in PostgreSQL/Oracle, or an "indexed view" in SQL Server) is a view whose result is physically stored, like a table, complete with its own indexes. Materialized views trade storage and maintenance overhead for query speed: reading a materialized view is a simple table scan/index lookup, whereas reading a virtual view means re-executing potentially expensive joins and aggregations every time. A third concept, updatability, asks: if a user issues `UPDATE DeptSummary SET avg_salary = 50000 WHERE dept_id = 3`, does that even make sense? Updating a view means updating data that is derived, so the DBMS must be able to translate the requested change into an unambiguous change on the base table(s). Some views (simple projections/selections of a single table, retaining the primary key) support this cleanly; views involving aggregation, `DISTINCT`, joins, or set operations generally do not, because the mapping from view rows back to base rows is either lossy, many-to-one, or undefined.

View expansion is the mechanism that makes virtual views work, and it is best understood as a rewriting step performed before (or during) query optimization. Given a query `Q` that references view `V`, and `V`'s stored definition `def(V)`, the processor: 1. Parses `Q` into an internal representation (typically a relational-algebra expression tree or an annotated parse tree). 2. For every leaf node in that tree that is a reference to view `V`, replaces the leaf with the (also parsed) expression tree of `def(V)`, renaming attributes/aliases as needed to avoid collisions. 3. Repeats this recursively if `def(V)` itself references other views (views can be nested/stacked). 4. Hands the fully expanded, view-free expression tree to the query optimizer, which treats it as an ordinary query over base tables — free to reorder joins, push down selections, choose indexes, etc., across the former view boundary. This last point matters: expansion happens before optimization, not after, precisely so the optimizer isn't blocked from optimizing across view boundaries. If a user writes `SELECT FROM DeptSummary WHERE dept_id = 3`, naive execution would compute the aggregate over all* departments and then filter — wasteful. But after expansion, the optimizer sees a single query with both the `GROUP BY` and the `WHERE dept_id = 3` predicate, and (subject to correctness of predicate pushdown past aggregation, which holds when the predicate is on a grouping column) can push the selection below the aggregation, filtering rows before grouping.

View updates work by an analogous but stricter mechanism: the processor must translate an `INSERT`/`UPDATE`/`DELETE` on the view into one on the base table(s). SQL standard and most systems apply a syntactic test for whether a view is updatable, roughly: ``` A view V, defined as SELECT ... FROM T [WHERE ...], is UPDATABLE if: 1. V's FROM clause references exactly one updatable table or view (no joins). 2. V's SELECT list contains no aggregate functions, no GROUP BY, no HAVING. 3. V's SELECT list contains no DISTINCT. 4. V's SELECT list contains no set operations (UNION, INTERSECT, EXCEPT), unless it is UNION ALL of updatable views on disjoint conditions (implementation-dependent). 5. Every column of T that lacks a default value and does not allow NULL is either exposed in V's SELECT list, or is not required for the specific DML operation (e.g., not required for UPDATE of other columns). 6. Any subquery in the WHERE clause does not reference the same table T that is being updated (to avoid re-entrant ambiguity), depending on implementation. ``` If these hold, the DBMS rewrites `UPDATE V SET c = expr WHERE p` into `UPDATE T SET c = expr WHERE p AND view_condition`, where `view_condition` is `V`'s own `WHERE` clause translated to reference `T`. If they fail — say `V` joins two tables, or aggregates — the view is not updatable, and any DML against it raises an error (or, in some systems, must be redirected through an `INSTEAD OF` trigger that the DBA writes by hand, explicitly encoding the intended semantics). A second refinement is `WITH CHECK OPTION`: ```sql CREATE VIEW ActiveEmployee AS SELECT FROM Employee WHERE status = 'ACTIVE' WITH CHECK OPTION; ``` Without `CHECK OPTION`, a user could `UPDATE ActiveEmployee SET status = 'RETIRED' WHERE emp_id = 42`; this update succeeds on the base table, but the resulting row no longer satisfies* the view's `WHERE` clause and vanishes from `ActiveEmployee` immediately after being modified through it — a classic surprise. `WITH CHECK OPTION` makes the DBMS reject any insert/update that would produce a row failing the view's own predicate, closing this hole.

Materialized view maintenance is a distinct algorithmic problem: once `MV` is materialized, how does the system keep it consistent as base tables change? Two families of strategy exist:

• Full recomputation: drop and rebuild `MV` from scratch by re-running its defining query. Simple, always correct, but $O(\text{size of the full re-evaluation})$ — potentially very expensive for large base tables, and typically done on a schedule (nightly `REFRESH MATERIALIZED VIEW`) rather than on every write.
• Incremental view maintenance (IVM): given a delta $\Delta T$ (the set of inserted/deleted/updated rows) applied to base table $T$, compute a corresponding delta $\Delta MV$ to apply to the materialized view, without re-scanning all of $T$. For example, if $MV = \sigma_p(T)$ (a selection), then $\Delta MV = \sigma_p(\Delta T)$ — trivially incremental. For joins, $\Delta(R \bowtie S) = (\Delta R \bowtie S) \cup (R \bowtie \Delta S) \cup (\Delta R \bowtie \Delta S)$, letting the system touch only the changed rows and their join partners, not the entire relations. For aggregates like `SUM`/`COUNT`, incremental maintenance keeps per-group running totals and adjusts them by the delta's contribution — but `AVG` requires maintaining both `SUM` and `COUNT` internally to update correctly, and `MIN`/`MAX` are the hard case: removing the current minimum forces a re-scan of that group unless auxiliary structures (like a sorted index) are kept.

```pseudocode // Full re-evaluation refresh function REFRESH_FULL(MV): result = EVALUATE(def(MV)) // execute the defining query fresh MV.storage = result MV.indexes = REBUILD_INDEXES(result) // Incremental view maintenance for MV = R JOIN S function REFRESH_INCREMENTAL_JOIN(MV, deltaR_insert, deltaR_delete, deltaS_insert, deltaS_delete): // Apply deletions first, then insertions, to avoid double counting if deltaR_delete is not empty: MV.remove( JOIN(deltaR_delete, S) ) if deltaS_delete is not empty: MV.remove( JOIN(R, deltaS_delete) ) if deltaR_insert is not empty: MV.add( JOIN(deltaR_insert, S) ) if deltaS_insert is not empty: MV.add( JOIN(R, deltaS_insert) ) // Note: JOIN(deltaR_insert, deltaS_insert) is naturally included because // by the time deltaS_insert is applied, S already reflects deltaR's effect // if updates are interleaved correctly; production systems order this // carefully to avoid missing or double-counting the delta-delta term. // Incremental maintenance for MV = SELECT g, SUM(x), COUNT(*) FROM T GROUP BY g function REFRESH_INCREMENTAL_AGG(MV, deltaT_insert, deltaT_delete): for each row r in deltaT_delete: group = MV.lookup(r.g) group.sum -= r.x group.count -= 1 if group.count == 0: MV.remove(group) // group vanished entirely for each row r in deltaT_insert: group = MV.lookup(r.g) if group does not exist: MV.insert(g = r.g, sum = r.x, count = 1) else: group.sum += r.x group.count += 1 ``` The engine chooses which strategy per view (and sometimes hybridizes: incremental for small deltas, periodic full refresh to bound drift/fragmentation), and this choice is itself informed by the query-processing machinery covered in the Query Processing Overview.

View expansion cost. Expansion itself is cheap: it is a tree substitution operation, $O(n)$ in the size of the query's internal representation (number of nodes), because each reference to a view is replaced once, and nesting depth is bounded by the number of distinct views in the schema (cyclic view definitions are disallowed by the catalog, so expansion always terminates). The real cost is whatever the optimizer subsequently does with the larger expanded tree — join-order search, which is the exponential/NP-hard part of query optimization discussed under Query Processing Overview — but that cost is identical to writing the expanded query by hand; views add no asymptotic overhead of their own beyond the linear substitution.

Correctness of expansion. The substitution is correct by a simple equational argument: a view is defined as an equation, $V \equiv \text{def}(V)$, over the relational algebra. Relational algebra respects referential transparency — replacing any subexpression by an equal one yields an equal result — so substituting $\text{def}(V)$ for every occurrence of $V$ produces an expression provably equivalent to the original query, for any database instance. This is why the optimizer is free to push predicates, reorder joins, and apply any algebraic rewrite rule across the former view boundary: the boundary was never semantically meaningful, only syntactically convenient. This is also why nested/stacked views (a view defined over other views) work correctly by simple induction on nesting depth: expand the innermost view first, then treat the result as an ordinary base-table reference for the next level.

Correctness of updatable-view translation. The syntactic criteria for updatability exist precisely to guarantee a unique, well-defined inverse mapping from a view row to a base row. Consider why each restriction is necessary:

• No joins in an updatable view: if `V = R ⋈ S`, then inserting a new tuple into `V` would require simultaneously inventing consistent new tuples for both `R` and `S` — there is no canonical way to split one row into two without extra information the system doesn't have. (Some systems relax this for very restricted join shapes, like a view over a single "primary" table and a lookup table joined on a foreign key, but general joins are excluded.)
• No aggregation: `AVG(salary) = 50000` for a department is consistent with infinitely many underlying salary distributions; `UPDATE DeptSummary SET avg_salary = 50000` has no unique inverse image on `Employee`. Aggregation is inherently lossy (many base states map to one view state), which is exactly what makes it useful for reporting and exactly what makes it non-invertible.
• No `DISTINCT`: duplicate elimination is lossy in the same sense — if two identical rows collapse to one in the view, deleting "that" view row is ambiguous (delete one underlying copy? both?).
• `WHERE` clause becomes part of the translated predicate, and `CHECK OPTION` closes the "disappearing row" loophole: without it, the system permits an update that is locally valid on the base table but violates the implicit contract "this view only shows rows satisfying $p$" — a correctness property of the view's interface, not the base data.

Formally, updatability corresponds to whether the view's defining query is invertible as a partial function on database instances restricted to the relevant tuple: does there exist a well-defined function mapping a modification of $V$'s extension to a unique modification of $T$'s extension that induces exactly that change (and no other) in $V$? Selections and simple projections that retain a key are invertible in this sense; aggregation, `DISTINCT`, and general joins are not, and the SQL standard's syntactic rules are a conservative (sound but not complete) approximation of this semantic property — they never permit an unsafe update, but they do sometimes forbid updates that a human could argue are "obviously fine" in special cases, trading completeness for a simple, checkable rule.

Materialized view maintenance complexity. Full recomputation costs exactly the cost of evaluating $\text{def}(MV)$ from scratch — call this $C(\text{def})$, dominated by the largest join/scan in the query, typically $O(N \log N)$ for a sort-merge join over $N$ rows or $O(N)$ expected for a hash join. Incremental maintenance costs are proportional to the size of the delta, not the size of the base tables: for a selection, $O(|\Delta T|)$; for a join, $O(|\Delta R| \cdot |S| + |R| \cdot |\Delta S|)$ in the naive nested-loop form, but typically $O((|\Delta R| + |\Delta S|) \log(|R|+|S|))$ using indexes to look up join partners for just the delta rows — a substantial win when $|\Delta T| \ll |T|$, which is the common case in an OLTP system with a large stable table and a small daily batch of changes. The correctness of the incremental join formula

follows directly from bilinearity of the join operator over set union/difference, and production systems must apply deletions before insertions (as in the pseudocode above) to avoid transiently counting a row twice or dropping the delta-delta cross term — a genuine and well-known implementation subtlety.

Tradeoffs, stated honestly. Materialized views speed up reads at the cost of (a) storage, (b) write amplification (every base update may trigger maintenance work on every dependent materialized view), and (c) potential staleness if refresh is deferred/asynchronous — a materialized view is a form of denormalization and inherits denormalization's classic consistency-vs-performance tension. Virtual views have zero staleness and zero storage cost but zero speedup; they are purely a logical/security convenience, not a performance one, unless the optimizer's rewrites happen to produce a cheap plan anyway.

Consider a simplified schema: ``` Employee(emp_id, name, dept_id, salary, status) Department(dept_id, dept_name, budget) ``` Sample data:

Define two views: ```sql CREATE VIEW ActiveEmployee AS SELECT emp_id, name, dept_id, salary FROM Employee WHERE status = 'ACTIVE' WITH CHECK OPTION; CREATE MATERIALIZED VIEW DeptSummary AS SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS headcount FROM Employee GROUP BY dept_id; ```

Step 1 — updatability check on `ActiveEmployee`. Single base table, no aggregation, no `DISTINCT`, no joins, primary key `emp_id` retained → updatable. A user runs: ```sql UPDATE ActiveEmployee SET salary = 95000 WHERE emp_id = 1; ``` This translates to `UPDATE Employee SET salary = 95000 WHERE emp_id = 1 AND status = 'ACTIVE'`, which succeeds (Ana is active). Now the user tries: ```sql UPDATE ActiveEmployee SET status = 'RETIRED' WHERE emp_id = 2; ``` Without `CHECK OPTION`, this would silently succeed on `Employee` and Ben would vanish from `ActiveEmployee`. With `CHECK OPTION`, the DBMS evaluates whether the post-update row satisfies `status = 'ACTIVE'` — it doesn't — and rejects the statement with an error, exactly as designed.

Step 2 — expansion for a query. ```sql SELECT name FROM ActiveEmployee WHERE dept_id = 10; ``` Expands to: ```sql SELECT name FROM Employee WHERE status = 'ACTIVE' AND dept_id = 10; ``` Result: `{Ana, Ben}` (Ana and Ben are both active, dept 10). Cid, Dee, Eli are excluded by either the department filter or the status filter — correctly, since the view's `WHERE` and the query's `WHERE` are conjoined by expansion, exactly as the algebra predicts.

Step 3 — materialized view initial state. `DeptSummary` (materialized, over all employees regardless of status, per its own definition):

Step 4 — incremental maintenance on insert. A new employee is hired: ```sql INSERT INTO Employee VALUES (6, 'Fay', 10, 100000, 'ACTIVE'); ``` The delta is $\Delta T_{\text{insert}} = \{(6,\text{Fay},10,100000,\text{ACTIVE})\}$. Running `REFRESH_INCREMENTAL_AGG`:

• Group `dept_id = 10` exists: `sum` was $90000+70000=160000$, becomes $160000+100000=260000$; `count` was 2, becomes 3.
• New `avg_salary = 260000/3 ≈ 86666.67`.

Updated materialized view, touching only one row, without rescanning departments 20 or 30:

This demonstrates the efficiency claim concretely: the refresh touched $O(1)$ existing group plus the one new row, rather than re-scanning all six employee rows and recomputing every group from scratch — the payoff of incremental maintenance grows with table size while the delta stays small.

The view concept traces directly to the foundational work on the relational model and the three-schema architecture (external, conceptual, internal), which formally separates what an application sees from how data is physically stored — views are the primary mechanism implementing the external/logical boundary in every SQL system since System R.

Views are load-bearing infrastructure across nearly every production database system. Business intelligence and reporting tools (Tableau, Looker, Power BI) almost never query raw normalized tables directly; they query curated views or materialized views that pre-join and pre-aggregate data into report-friendly shapes, hiding schema complexity from analysts. Row-level and column-level security in systems like PostgreSQL, Oracle, and SQL Server is frequently implemented via views combined with grants — a user is given access only to a filtering view (`WHERE tenant_id = current_user_tenant()`), never to the base table, so multi-tenant SaaS applications rely on exactly the `WITH CHECK OPTION` reasoning discussed above to keep tenants from reading or corrupting each other's data. Data warehouses and OLAP cubes are, at their core, elaborate materialized views: star-schema fact tables are pre-aggregated along common dimensions (time, region, product) precisely because recomputing these aggregates from raw transactional logs at query time would be prohibitively slow — this is the same incremental-maintenance machinery scaled up, often called "summary tables" or "rollups" in warehouse literature. Modern cloud data warehouses (Snowflake, BigQuery, Redshift) expose materialized views as a first-class performance feature with automatic incremental refresh and even automatic query rewriting, where the optimizer detects that a user's ad hoc query could be partially answered from an existing materialized view and substitutes it in — a direct descendant of the view-expansion mechanism, run in reverse. Database migration and schema-evolution tools also use views as a compatibility shim, letting old application code keep querying a stable view interface while the underlying physical schema is refactored beneath it — the same separation of concerns that motivates studying view expansion alongside the broader Query Processing Overview and the procedural logic layered on top in Triggers and Stored Procedures.

Ask the tutor: Can you walk through why a view with `UNION ALL` of two updatable sub-views is sometimes considered updatable, while one with plain `UNION` never is?