Database Views: Virtual Tables and Their Practical Applications

Database views are a foundational construct in relational database systems, functioning as named, stored query definitions that present data as if it were a physical table. This page covers the structural definition of views, their execution mechanics, the professional scenarios in which they are deployed, and the boundaries that separate views from more appropriate alternatives. The subject spans standard SQL environments including PostgreSQL, Oracle Database, and Microsoft SQL Server, and connects directly to topics such as database security and access control, query optimization, and schema design.

Definition and scope

A database view is a virtual table defined by a stored SELECT statement. It holds no data of its own — the underlying base tables retain all persistent storage. When queried, the database engine substitutes the view's definition and executes against the source tables in real time. This behavior is codified across the major SQL standards published by ISO/IEC, specifically ISO/IEC 9075, the foundational SQL standard that has governed relational query language specification since its first release and through revisions including SQL:1999, SQL:2003, and SQL:2016 (ISO/IEC JTC 1/SC 32).

Two primary classifications of views exist within this standard:

Simple views query a single base table without aggregation, grouping, or subqueries. In most conforming systems, simple views support DML operations (INSERT, UPDATE, DELETE) directly, meaning changes through the view propagate to the underlying table.

Complex views involve joins across multiple tables, aggregation functions, DISTINCT clauses, subqueries, or set operations. Complex views are generally not updatable. Attempting DML against a non-updatable view produces an error in PostgreSQL, Oracle, and SQL Server.

A third variant — materialized views — breaks from the virtual-table model by physically storing the result set of the defining query. Materialized views introduce a persistence layer absent from standard views and carry distinct refresh semantics. Oracle Database refers to these as Materialized Views; PostgreSQL uses the same term; SQL Server implements comparable functionality through Indexed Views, which require the SCHEMABINDING option and restrict the allowable query constructs.

The scope of view functionality also intersects stored procedures and triggers when views are paired with INSTEAD OF triggers to simulate updatability in otherwise non-updatable complex views — a pattern supported in Oracle and SQL Server.

How it works

View execution in a standard relational database follows a query rewrite and substitution model:

1. View resolution — The parser identifies the view name in an incoming query and retrieves the stored view definition from the system catalog (e.g., pg_views in PostgreSQL, ALL_VIEWS in Oracle).
2. Query rewrite — The query optimizer replaces the view reference with the underlying SELECT statement, merging it with any predicates from the outer query.
3. Optimization — The combined query tree passes through the cost-based optimizer, which evaluates execution plans against available indexes on the base tables. Views themselves carry no independent indexes in the standard (non-materialized) model.
4. Execution — The query executes against the base tables using the selected plan, and results are returned without any intermediate persistence.

This model has a critical implication for database performance tuning: views do not inherently improve performance. Because execution reaches back to the base tables on every call, a poorly optimized view definition imposes the same cost as the equivalent direct query. Performance depends entirely on whether the base tables carry appropriate indexes matching the predicates used at query time.

Materialized views invert this dynamic. Refresh cycles — either on-demand or scheduled — pre-compute and store results. Oracle Database supports ON COMMIT refresh for near-real-time consistency. PostgreSQL requires explicit REFRESH MATERIALIZED VIEW commands or scheduled jobs. The tradeoff against freshness is measurable: a materialized view queried 10 seconds after a base-table update may return stale results depending on the refresh interval configured.

Common scenarios

Database views appear across four recurring professional contexts:

Access control and data masking — Views expose a filtered or column-restricted projection of a table to a specific database role without granting direct table access. A view can omit columns containing personally identifiable information (PII) or salary data, enforcing a least-privilege model aligned with NIST SP 800-53 Rev 5 control AC-3 (Access Enforcement) (NIST SP 800-53). This connects directly to database auditing and compliance workflows where access segmentation must be demonstrable.

Simplifying complex join logic — Reporting layers and applications frequently need multi-table join results. A view consolidates a 4-table join into a single named object, reducing query complexity in application code and lowering the risk of inconsistent join predicates across codebases. This pattern is common in data warehousing environments where denormalized reporting structures must coexist with normalized transactional schemas.

Logical abstraction during schema migration — When base table structures change, views can maintain a stable interface for dependent applications. A database migration that renames or restructures a column can preserve the original column name in the view definition, shielding application code from immediate refactoring. The comprehensive reference landscape for database systems — including how views fit into the broader architecture — is described at the Database Systems Authority.

Aggregation and reporting shortcuts — Views encapsulating GROUP BY logic give reporting users a consistent aggregated dataset. In OLTP vs OLAP environments, materialized views often serve as the bridge layer, pre-aggregating transactional data into analytical-ready summaries.

Decision boundaries

Choosing between a standard view, a materialized view, or an alternative construct depends on discrete factors:

Standard views are appropriate when data must reflect the current state of base tables and query frequency is moderate. Materialized views are appropriate when the underlying query is computationally expensive, data freshness tolerance is measured in minutes or hours, and refresh scheduling is operationally manageable.

Common table expressions (CTEs), introduced in SQL:1999 and supported across PostgreSQL, SQL Server, and Oracle, provide similar query-simplification benefits to simple views but are session-scoped and carry no persistence. CTEs are the preferred pattern when a logical subquery is needed once within a single query rather than reused across sessions or applications.

Views are not substitutes for normalization or database partitioning. A view over a poorly normalized base schema does not correct underlying data integrity issues; it layers abstraction over a structural problem. Similarly, views over very large tables without partition pruning can produce full-scan execution plans that degrade under load — a failure mode that requires evaluation during schema review and database monitoring.

References

• ISO/IEC JTC 1/SC 32 — Data Management and Interchange
• NIST SP 800-53 Rev 5 — Security and Privacy Controls for Information Systems and Organizations
• PostgreSQL Documentation — CREATE VIEW
• PostgreSQL Documentation — Materialized Views
• Oracle Database SQL Language Reference — CREATE MATERIALIZED VIEW
• Microsoft SQL Server Documentation — CREATE VIEW (Transact-SQL)