Transactions & Programmability
Views
A view is a named, stored query that behaves like a virtual table.
Overview
A view is a named, stored query that behaves like a virtual table. It does not normally hold data of its own; instead, the database resolves its defining query when the view is referenced. Views were standardized in SQL-92 and remain a core feature of virtually every SQL implementation. They serve several purposes: simplifying complex queries, presenting a stable interface over changing base tables, restricting column or row exposure for security, and encapsulating join logic so application code can select from a single name. A view can be queried with SELECT, joined to other tables and views, and in many systems updated under defined conditions. Because the definition is stored rather than the result, a view always reflects the current state of its underlying tables. Some products support materialized views, which persist computed results and require refresh, but that is an extension beyond the SQL-92 view concept. Understanding the distinction between a regular view and a materialized view is important when reasoning about performance and consistency.
Syntax and usage
The standard form creates a view from a query: CREATE VIEW view_name [(column_list)] AS SELECT .... The optional column list renames the view's columns without changing the underlying query. CREATE OR REPLACE VIEW is widely supported but not universal in SQL-92; check the specific product. Dropping a view uses DROP VIEW view_name, sometimes with CASCADE or RESTRICT to control dependent objects. Views are used wherever a table reference is allowed: in SELECT, INSERT, UPDATE, DELETE, in joins, and as the source for another view. Whether a view is updatable depends on its definition. Simple views over one table with no aggregation, DISTINCT, or grouping are generally updatable; complex views often are not. Many systems support WITH CHECK OPTION to prevent inserts or updates that would fall outside the view's WHERE clause. Some dialects add CREATE MATERIALIZED VIEW with a refresh clause, but this is vendor-specific and not part of SQL-92.
How it works
When a statement references a view, the database expands the view definition into the surrounding query, a process called view resolution or merging. The optimizer then plans the combined query against the base tables, so predicates from the outer query can often be pushed down into the view for efficiency. This means a view is not a stored result set; it is a stored expression. Consequently, querying a view costs roughly what the equivalent expanded query costs. If the view contains aggregation, DISTINCT, or set operations, merging may be impossible and the system may materialize the view's result internally before applying the outer query. Access control is typically evaluated on the view, allowing users to be granted rights on the view without direct rights on base tables. Because resolution happens at execution time, schema changes to base tables can invalidate a view; many systems track dependencies and either block the change or mark the view invalid until recompiled.
Examples
Create a view that exposes only active customers: CREATE VIEW active_customers AS SELECT customer_id, name, email FROM customers WHERE status = 'active';. Query it like a table: SELECT name FROM active_customers WHERE email IS NOT NULL;. A view encapsulating a join: CREATE VIEW order_summary AS SELECT o.order_id, c.name, o.total FROM orders o JOIN customers c ON c.customer_id = o.customer_id;. An updatable view with a check: CREATE VIEW eu_customers AS SELECT * FROM customers WHERE region = 'EU' WITH CHECK OPTION;. Inserting through it: INSERT INTO eu_customers (customer_id, name, region) VALUES (10, 'Ana', 'EU'); succeeds, while a row with a non-EU region is rejected. Replace a definition: CREATE OR REPLACE VIEW active_customers AS SELECT customer_id, name FROM customers WHERE status = 'active';. Remove it: DROP VIEW active_customers;. These examples use standard syntax; materialized variants differ by product.
Common pitfalls
A frequent mistake is assuming a view stores data. Regular views recompute on each reference, so heavy views can be slow. Another pitfall is expecting every view to be updatable; views with joins, aggregates, DISTINCT, or expressions in the select list often reject INSERT, UPDATE, or DELETE. Column ambiguity arises when a view's query joins tables with identically named columns, so an explicit column list is advisable. Nesting views deeply can obscure the real query plan and make tuning difficult. Relying on SELECT * in a view definition is risky because adding a column to a base table can change the view's shape or break dependent code. Security assumptions can also fail: a view restricts columns only if it omits them, and row filtering depends on the WHERE clause being correct. Finally, schema changes to underlying tables may invalidate views, and behavior on invalidation varies across systems.
Best practices
Give views clear, purpose-driven names and document their intent. Specify an explicit column list rather than SELECT * so the interface stays stable when base tables evolve. Keep view definitions focused; a view that does too much is hard to reuse and tune. Use views to centralize security and to present a stable API over volatile schemas. When a view must be updatable, keep it simple and consider WITH CHECK OPTION to enforce the intended row scope. Avoid deep chains of views on views, which complicate optimization; prefer a single well-structured definition or a common table expression for one-off logic. Monitor performance of frequently used views and compare against the expanded query. In systems that support materialized views, use them deliberately for expensive aggregations and define a refresh strategy, recognizing the trade-off between staleness and speed.
Frequently asked questions
Does a view store data? A regular view stores only its defining query, not rows; results are computed when the view is queried. Materialized views, available in some products, do store results and need refreshing. Can I insert into a view? Only if the view is updatable, which generally requires a single base table with no aggregation, DISTINCT, or grouping; WITH CHECK OPTION can constrain which rows are allowed. Is a view faster than a query? Not inherently. Because the definition is merged into the outer query, performance is usually similar to writing the equivalent query directly; benefits come from reuse, clarity, and access control rather than raw speed. Do views improve security? They can, by exposing only selected columns or rows and by granting privileges on the view instead of base tables, provided the definitions are written carefully.