Views

Introduction

View: virtual table (saved query). Abstraction: hide complexity. Security: restrict access (columns/rows). Simplification: pre-join tables. Logical layer: above physical schema.

Query: SELECT from view looks like table. Internally: executes saved query. Cost: depends (materialized vs. virtual). Flexibility: powerful abstraction mechanism.

"Views separate logical interface from physical schema. Abstraction: simplify applications, enforce security, enable schema evolution. Virtual tables: elegant abstraction." -- SQL design

View Definition

Concept

Stored SELECT query: named, reusable. No data stored: query executed on access. Virtual: appears as table, behaves as table.

Simple Example

CREATE VIEW high_earners AS
SELECT name, salary
FROM employees
WHERE salary > 100000;

Metadata

System stores: view definition (SQL), columns, permissions. Query plan: recomputed (or cached). Transparent: users see table.

Schema Information

Information schema: describes views (like tables). Column names: from SELECT. Data types: from underlying tables.

Creating Views

Syntax

CREATE VIEW view_name AS
SELECT columns FROM tables WHERE conditions;

Complex View

CREATE VIEW employee_departments AS
SELECT e.name, e.salary, d.dept_name, d.location
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

Column Aliases

CREATE VIEW sales_summary AS
SELECT
 product_id AS product,
 COUNT(*) AS units,
 SUM(amount) AS revenue
FROM sales
GROUP BY product_id;

Dropping Views

DROP VIEW view_name;
DROP VIEW view_name CASCADE; -- Drop dependents (careful)

Modification

CREATE OR REPLACE VIEW: update definition. Constraints: compatible columns (same order, types). Safe: old applications still work.

Querying Views

Transparent Usage

SELECT * FROM high_earners;
(Works like table: internally executes saved query)

Filtering

SELECT * FROM high_earners WHERE name LIKE 'A%';

Joining

SELECT * FROM high_earners
JOIN departments ON ...;

Subquery

SELECT AVG(salary) FROM high_earners;

Restrictions

Some views non-updatable (complex queries). Some systems: read-only views. Verify: documentation (database-specific).

Benefits

Simplification

Hide complexity: pre-join tables, apply filters. Users: query simple interface. Encapsulation: implementation hidden.

Security

Restrict data: show subset (columns/rows). Users: cannot see hidden. Example: managers see salaries, others don't. Row-level security: views enable.

Consistency

Standard definition: all use same logic. Bug fix: one place (view definition). Consistency: guaranteed (not per-query).

Maintenance

Schema change: handle in view. Applications: unchanged (still query view). Backward compatibility: views facilitate.

Logical Independence

Physical restructuring: doesn't affect applications (if view mapped properly). Evolution: schema adapts (views mediate).

Updatable Views

Definition

UPDATE/INSERT/DELETE on view: affects underlying table. Restrictions: not all views updatable (complex queries).

Conditions for Updateability

Single table: must reference one table. No aggregates: COUNT, SUM, etc. forbidden. No DISTINCT. No GROUP BY/HAVING. Simple: directly mappable to table.

Example Updatable

CREATE VIEW dept_10_employees AS
SELECT emp_id, name, salary
FROM employees
WHERE dept_id = 10;

UPDATE dept_10_employees SET salary = salary * 1.1;
(Works: affects underlying employees table)

Non-Updatable Example

CREATE VIEW emp_by_dept AS
SELECT dept_id, COUNT(*) AS count
FROM employees
GROUP BY dept_id;

UPDATE emp_by_dept SET count = 10;
(Fails: no underlying row corresponds to aggregate)

WITH CHECK OPTION

CREATE VIEW dept_10 AS
SELECT * FROM employees WHERE dept_id = 10
WITH CHECK OPTION;

(Enforces: updates maintain dept_id = 10 condition)

Materialized Views

Concept

Stores results: actual data (not just query). Faster: pre-computed. Staleness: results may be outdated. Refresh: periodic update.

Creation

CREATE MATERIALIZED VIEW sales_by_region AS
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

Refresh

REFRESH MATERIALIZED VIEW sales_by_region;
(Re-executes query, updates stored data)

Benefits

Speed: pre-computed results (no query execution). Analytics: complex aggregations. Reporting: predictable performance.

Costs

Storage: stores results (space). Staleness: data lag (refresh frequency). Maintenance: refresh overhead. Balance: trade-offs.

Use Cases

Dashboards: pre-computed metrics. Reports: aggregated data. OLAP: cubes. Data warehouse: summary tables.

Performance Considerations

Virtual Views

Query expansion: subquery in FROM (like derived table). Cost: depends on subquery complexity. Optimizer: sometimes materializes (temp).

Index Usage

Underlying table indexes: used (if query allows). View columns: indexed via table (transparent).

Materialized Views

Query cost: none (pre-computed). Refresh cost: periodic (batch). Staleness: acceptable? (domain-dependent).

Optimization

EXPLAIN: show actual plan (not stored query). Complex views: may not optimize well. Simplify: if performance poor.

Security Applications

Row-Level Security

CREATE VIEW employee_data AS
SELECT * FROM employees
WHERE dept_id IN (SELECT dept_id FROM user_departments
 WHERE user = CURRENT_USER);

Column-Level Security

CREATE VIEW public_employees AS
SELECT name, position FROM employees;
(Hides: salary, personal info)

Access Control

GRANT: view permission (not underlying table). Transparent: users unaware (just see view). Enforcement: database layer.

Audit Trail

View definition: documented access policy. Changes: tracked. Enforcement: automatic (via view logic).

Limitations

Updateability Restrictions

Complex views: non-updatable. Workaround: INSTEAD OF triggers (advanced). Cost: complexity increases.

Performance Degradation

Nested views: complex query (deep nesting). Optimization: challenging. Cost: may be slow (needs measurement).

Staleness (Materialized)

Data lag: inherent with materialization. Freshness: requires frequent refresh (cost). Balance: needed.

Dependency Management

Schema change: view may break (if column removed). Cascading: dependent views affected. Careful: modification strategy needed.

Clarity

Too many views: confusing. Documentation: needed (what each view for?). Governance: manage view explosion.

Practical Examples

Sales Dashboard

CREATE VIEW daily_sales AS
SELECT
 DATE(order_date) AS day,
 COUNT(*) AS orders,
 SUM(total) AS revenue,
 AVG(total) AS avg_order
FROM orders
GROUP BY DATE(order_date);

Employee Summary

CREATE VIEW dept_summary AS
SELECT
 d.dept_name,
 COUNT(*) AS emp_count,
 AVG(e.salary) AS avg_salary,
 MAX(e.salary) AS max_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

Security View

CREATE VIEW my_department_data AS
SELECT emp_id, name, salary
FROM employees
WHERE dept_id = (SELECT dept_id FROM employees
 WHERE user_id = CURRENT_USER);

References

• Ramakrishnan, R., and Gehrke, J. "Database Management Systems." McGraw-Hill, 3rd edition, 2003.
• ISO/IEC 9075-1:2016 Information Technology - Database Languages - SQL.
• Garcia-Molina, H., Ullman, J. D., and Widom, J. "Database Systems: The Complete Book." Pearson, 2nd edition, 2008.