View Definition: Understanding Virtual Tables - SQL Views: Virtual Tables and Data Abstraction | OneNoughtOne

0/278

Database Management SystemsViews

SQL Views: Virtual Tables and Data Abstraction

LevelIntermediate

Duration55 mins

TopicViews

1 / 5

View Definition: Understanding Virtual Tables

The Power of Abstraction

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.

What You Will Learn

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.

What Is a View?

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.

View vs Table: The Fundamental Distinction

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.

Views in the Three-Schema Architecture

To fully appreciate views, we must understand their position in database architecture. The ANSI/SPARC three-schema architecture defines three levels of abstraction:

Internal Schema (Physical Level): How data is physically stored—file structures, indexes, storage allocation
Conceptual Schema (Logical Level): The logical structure of the entire database—tables, columns, constraints, relationships
External Schema (View Level): Individual user or application perspectives on subsets of the database

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.

Three-Schema Architecture and Views
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.

The Power of Indirection

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.

Anatomy of a View

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:

Column selections and expressions
WHERE filtering
JOINs across multiple tables
GROUP BY aggregations
Subqueries and CTEs
Window functions

4. View Metadata: Information stored in the system catalog about the view: owner, creation date, dependencies, permissions.

view_anatomy.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Components of a view definition
CREATE VIEW view_name (column1, column2, column3)  -- Name + optional column list
AS
    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 view
CREATE 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;

Column Naming Matters

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.

How Views Work Internally

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):

Parse the user query containing the view reference
Retrieve the view definition from the system catalog
Substitute the view reference with the view's defining query
Merge the expanded query with the original query's predicates, projections, and joins
Optimize the merged query as a single unit
Execute the optimized plan against base tables

view_expansion_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- View definition
CREATE VIEW v_active_customers AS
    SELECT id, name, email, signup_date
    FROM customers
    WHERE status = 'active' AND deleted_at IS NULL;
 
-- User query
SELECT name, email 
FROM v_active_customers 
WHERE signup_date > '2024-01-01';
 
-- After view expansion (conceptually):
SELECT name, email
FROM (
    SELECT id, name, email, signup_date
    FROM customers
    WHERE status = 'active' AND deleted_at IS NULL
) AS v_active_customers
WHERE signup_date > '2024-01-01';
 
-- After query optimization (predicates merged, subquery eliminated):
SELECT name, email
FROM customers
WHERE 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:

The latest data is always retrieved (no staleness)
If base table structure changes, views may become invalid
Query plans are generated fresh (or cached) for each query

View Optimization

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 vs Subqueries, CTEs, and Tables

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.

Comparing Views with Related Constructs
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

The Materialized Exception

*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.

View Dependencies and the Dependency Graph

Views create dependencies between database objects. Understanding these dependencies is crucial for database maintenance and evolution.

Dependency types:

View → Table: Every view depends on at least one base table
View → View: Views can be defined over other views, creating dependency chains
View → Functions: Views using functions depend on those functions
Other objects → View: Stored procedures, triggers, and applications may depend on views

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:

Warn before dropping objects that other objects depend on
Cascade drops when specified
Invalidate views when base structures change

dependency_chain.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Base tables
CREATE 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 categories
CREATE 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 filtering
CREATE VIEW v_expensive_products AS
    SELECT *
    FROM v_products_with_category
    WHERE price > 100.00;
 
-- Third-level view: aggregation over second view
CREATE 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!

Dependency Awareness

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:

PostgreSQL: SELECT * FROM pg_depend WHERE objid = 'view_name'::regclass;
SQL Server: sp_depends 'view_name' or query sys.sql_expression_dependencies
MySQL: INFORMATION_SCHEMA.VIEW_TABLE_USAGE
Oracle: ALL_DEPENDENCIES and USER_DEPENDENCIES views

Modern tools extract these dependencies automatically to visualize schema graphs and prevent accidental breakage.

The Virtual Table Illusion

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):

SELECT rows with arbitrary WHERE clauses
JOIN views to other tables or views
Nest views in subqueries
Apply aggregations, ordering, and limits
Grant SELECT permissions to users
Reference in application queries as table names
Use in most ORM frameworks transparently

What you cannot do with standard views:

CREATE INDEX on a view (no physical rows to index)
Always INSERT/UPDATE/DELETE (depends on view definition)
Store data independently of base tables
Optimize views independently of underlying tables

View Capabilities

•SELECT with any predicates
•JOIN with any relations
•Use in subqueries
•Apply aggregations
•GRANT/REVOKE permissions
•Reference from applications
•Define views over views
•Aliasing and renaming

View Limitations

•No indexes on standard views
•INSERT/UPDATE restrictions
•No independent storage
•Cannot have triggers*
•Computed on every access
•Performance tied to base tables
•Deep nesting can hurt optimizer
•Dependent on source objects

INSTEAD OF Triggers

*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.

Summary: Understanding View Fundamentals

We've established the foundational understanding of views—the database world's primary abstraction mechanism. Let's consolidate the key concepts:

Key Takeaways

•Views are named queries stored in the catalog — They define virtual tables that compute results from base tables on demand.
•Views implement the external schema — They provide the abstraction layer between users/applications and the physical database structure.
•Query expansion makes views transparent — The optimizer merges view queries with user queries, typically incurring no performance penalty.
•Views create dependencies — Changing or dropping base objects can invalidate views, requiring careful dependency management.
•The virtual table illusion is nearly complete — Views support most read operations identically to tables, with limitations only in writes and indexing.
•Views enable data independence — You can restructure base tables while preserving the view interface, insulating applications from change.

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.

Page Complete

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.