Loading learning content...
Imagine you're designing a database for a large enterprise. You have dozens of tables, complex relationships, and hundreds of columns. Now, different teams need access to this data: the sales team needs customer purchase summaries, HR needs employee performance metrics, and finance needs revenue reports. Each team requires a different perspective on the same underlying data.
Do you create redundant copies of data for each team? Do you expose the full complexity of your schema to everyone? Neither option is acceptable. The first wastes storage and creates synchronization nightmares. The second exposes sensitive data and overwhelms users with complexity they don't need.
Views solve this fundamental problem. They are the database world's answer to abstraction—a mechanism that lets you define virtual tables that look and behave like real tables but are actually computed from underlying base tables on demand.
By the end of this page, you will understand what views are, how they differ fundamentally from tables, their role in the database architecture, and why they represent one of the most powerful abstraction mechanisms in relational database systems.
A view is a named query stored in the database catalog that behaves as if it were a table. Unlike a table, which physically stores data on disk, a view stores only a query definition—a SELECT statement that describes how to derive data from one or more base tables.
When you query a view, the database management system executes the underlying query and returns results as if they came from an actual table. To the user or application, the distinction is invisible: you SELECT from a view exactly as you would from a table.
The formal definition:
A view is a virtual relation defined by a query over one or more base relations (tables) or other views. The view's content is not stored independently but is computed from the defining query each time the view is accessed.
A table (base relation) stores actual data tuples on disk. A view (derived relation) stores a query that produces tuples when executed. Tables consume storage proportional to data volume; views consume only the storage for their query definition, regardless of how much data they would return.
Conceptual model:
Think of views as saved queries with names. Instead of writing a complex JOIN every time you need a report, you define it once as a view. From that point forward, anyone can query the view by name, unaware of the complexity hidden beneath.
Etymology and terminology:
The term "view" comes from the idea that a view presents a particular perspective or viewpoint on the data. Different views of the same underlying data can present radically different perspectives—one view might show only public information, another might aggregate data into summaries, and a third might join data from multiple tables into a denormalized format.
To fully appreciate views, we must understand their position in database architecture. The ANSI/SPARC three-schema architecture defines three levels of abstraction:
Views implement the external schema. They provide a mechanism for each user group to see exactly the data they need, in exactly the format they need it, without exposing the full complexity or sensitivity of the conceptual schema.
| Schema Level | What It Represents | How Views Relate |
|---|---|---|
| Internal (Physical) | B-trees, heap files, pages, disk blocks | Views are transparent to this level—storage engine doesn't know about views |
| Conceptual (Logical) | Tables, columns, primary keys, foreign keys | Views are defined over conceptual schema objects (base tables) |
| External (View) | User-specific perspectives and subsets | Views are the external schema—customized windows into the database |
Data independence through views:
This architecture enables logical data independence—the ability to change the conceptual schema without affecting applications. If you restructure your base tables, you can modify the view definitions to maintain the same external interface. Applications querying the views remain unaffected.
Example scenario:
Suppose you split a large Customer table into CustomerBasic and CustomerDetails for performance reasons. Without views, every application query must be rewritten. With views, you create a Customer view that joins the two tables—applications continue querying Customer without modification.
Views add a layer of indirection between applications and physical data. This indirection is the source of their power—it enables abstraction, security, and evolution without disrupting existing systems.
A view consists of several components, each playing a specific role in defining the virtual table:
1. View Name:
The identifier by which the view is referenced. Like table names, view names must be unique within a schema and should follow naming conventions (often prefixed with v_ or vw_).
2. Column List (Optional): Explicit names for the columns produced by the view query. If omitted, column names are inherited from the SELECT clause.
3. Defining Query: The SELECT statement that computes the view's content. This query can include:
4. View Metadata: Information stored in the system catalog about the view: owner, creation date, dependencies, permissions.
123456789101112131415161718192021222324252627282930
-- Components of a view definitionCREATE VIEW view_name (column1, column2, column3) -- Name + optional column listAS SELECT -- Defining query begins t1.column_a AS column1, t1.column_b AS column2, t2.column_c AS column3 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id WHERE t1.status = 'active'; -- Defining query ends -- Example: Employee salary summary viewCREATE VIEW v_employee_salary_summary ( department_name, employee_count, total_salary, average_salary, max_salary)AS SELECT d.name AS department_name, COUNT(e.id) AS employee_count, SUM(e.salary) AS total_salary, AVG(e.salary) AS average_salary, MAX(e.salary) AS max_salary FROM employees e JOIN departments d ON e.department_id = d.id WHERE e.termination_date IS NULL GROUP BY d.id, d.name;When your SELECT includes expressions (like SUM(salary) or a + b), you must provide explicit column names—either in the column list or via AS aliases in the SELECT. Expression results without names create unnamed columns that cannot be referenced.
Understanding what happens when you query a view reveals why views are both powerful and sometimes performance-sensitive.
Query Processing with Views:
When you execute a query against a view, the DBMS typically uses view expansion (also called view substitution or query rewriting):
1234567891011121314151617181920212223242526
-- View definitionCREATE VIEW v_active_customers AS SELECT id, name, email, signup_date FROM customers WHERE status = 'active' AND deleted_at IS NULL; -- User querySELECT name, email FROM v_active_customers WHERE signup_date > '2024-01-01'; -- After view expansion (conceptually):SELECT name, emailFROM ( SELECT id, name, email, signup_date FROM customers WHERE status = 'active' AND deleted_at IS NULL) AS v_active_customersWHERE signup_date > '2024-01-01'; -- After query optimization (predicates merged, subquery eliminated):SELECT name, emailFROM customersWHERE status = 'active' AND deleted_at IS NULL AND signup_date > '2024-01-01';The key insight:
Modern query optimizers don't treat views as black boxes. They expand views and then optimize the entire query as if you had written it directly against base tables. This means well-designed views typically have zero runtime overhead compared to equivalent direct queries.
View resolution timing:
Views are resolved at query compilation time, not at view creation time. This means:
Since views are expanded before optimization, a query like SELECT * FROM v_summary WHERE dept = 'Sales' can have its filter pushed down into the view's aggregation if the optimizer supports it. You're not penalized for querying through a view rather than directly.
Views occupy a specific niche in the SQL landscape. Understanding how they compare to related constructs clarifies when to use each:
Views vs Inline Subqueries:
Both define derived data, but subqueries are defined within a single query and cannot be reused. Views are defined once and reused across unlimited queries. If you find yourself copying the same subquery into multiple queries, that's a strong signal to create a view.
Views vs Common Table Expressions (CTEs):
CTEs (WITH clauses) are named result sets scoped to a single query. They improve readability but disappear after the query completes. Views persist in the database and are available to all queries indefinitely.
Views vs Tables:
Tables store data physically. Views compute data dynamically. If data changes in base tables, the view immediately reflects those changes—there's no synchronization required. However, views require computation on every access, while tables provide instant retrieval of stored values.
| Feature | View | Subquery | CTE | Table |
|---|---|---|---|---|
| Persistence | Stored in catalog | Query-scoped only | Query-scoped only | Stored in catalog |
| Reusability | Unlimited queries | Single occurrence | Within one query | Unlimited queries |
| Data storage | Query definition only | None | None | Physical data |
| Data freshness | Always current | Always current | Always current | Static until modified |
| Permissions | Grantable independently | N/A | N/A | Grantable independently |
| Indexes | Not supported* | N/A | N/A | Fully supported |
| Dependencies | Tracked by DBMS | N/A | N/A | Independent |
*Standard views cannot be indexed, but materialized views (covered later in this module) physically store their results and can be indexed. This blurs the line between views and tables, offering view semantics with table-like performance.
Views create dependencies between database objects. Understanding these dependencies is crucial for database maintenance and evolution.
Dependency types:
The dependency graph:
In a complex database, dependencies form a directed acyclic graph (DAG). The DBMS tracks this graph in system catalogs, enabling it to:
1234567891011121314151617181920212223242526272829303132333435363738394041
-- Base tablesCREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(255), category_id INT, price DECIMAL(10,2)); CREATE TABLE categories ( id INT PRIMARY KEY, name VARCHAR(100)); -- First-level view: joins products with categoriesCREATE VIEW v_products_with_category AS SELECT p.id, p.name, c.name AS category_name, p.price FROM products p JOIN categories c ON p.category_id = c.id; -- Second-level view: built on first view, adds filteringCREATE VIEW v_expensive_products AS SELECT * FROM v_products_with_category WHERE price > 100.00; -- Third-level view: aggregation over second viewCREATE VIEW v_expensive_by_category AS SELECT category_name, COUNT(*) AS product_count, AVG(price) AS avg_price FROM v_expensive_products GROUP BY category_name; -- Dependency chain:-- products, categories-- ↓-- v_products_with_category-- ↓-- v_expensive_products -- ↓-- v_expensive_by_category -- Dropping 'products' table would invalidate ALL three views!Before modifying or dropping any database object—table, column, or view—always check what depends on it. Most DBMSs provide system catalog queries or commands to identify dependencies. Unexpected view breakage is a common source of production incidents.
Checking dependencies in major databases:
SELECT * FROM pg_depend WHERE objid = 'view_name'::regclass;sp_depends 'view_name' or query sys.sql_expression_dependenciesINFORMATION_SCHEMA.VIEW_TABLE_USAGEALL_DEPENDENCIES and USER_DEPENDENCIES viewsModern tools extract these dependencies automatically to visualize schema graphs and prevent accidental breakage.
Views are often described as "virtual tables," but what makes this illusion so complete? Let's examine all the ways views behave identically to tables—and the few ways they differ.
What you can do with views (same as tables):
What you cannot do with standard views:
*Some DBMSs (SQL Server, Oracle) support INSTEAD OF triggers on views, allowing you to intercept INSERT/UPDATE/DELETE operations and translate them into operations on base tables. This enables making complex views updatable.
We've established the foundational understanding of views—the database world's primary abstraction mechanism. Let's consolidate the key concepts:
What's next:
Now that you understand what views are and how they work, the next page covers the practical syntax for creating views. We'll explore the CREATE VIEW statement in depth, including column naming, query restrictions, and the various options different DBMSs provide.
You now understand the conceptual foundation of database views—what they are, how they fit into system architecture, how they work internally, and how they compare to related constructs. Next, we'll put this knowledge into practice with CREATE VIEW syntax.