Chapter 18: Views and Virtual Tables

Views are stored SQL queries that act like virtual tables. They simplify complex logic, present consistent query interfaces, and protect underlying schema details. This chapter covers how to create and maintain views, explains when they can be updated, introduces materialized views for stored query results, and outlines caching strategies for performance.

Creating and updating views

A view is defined with a CREATE VIEW statement that stores a query for later use. When queried, the database runs the underlying SQL dynamically, presenting results as a table.

-- Create a view of active customers
CREATE VIEW active_customers AS
SELECT customer_id, name, country
FROM customers
WHERE active = 1;

-- Query it like a table
SELECT * FROM active_customers;

Views can join multiple tables, filter rows, or include calculated columns. They are often used to encapsulate business logic or simplify repetitive queries.

Replacing or altering a view

To modify an existing view, use CREATE OR REPLACE VIEW where supported, or drop and recreate it.

-- PostgreSQL and Oracle
CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE active = 1;

-- MySQL (before 5.7) requires drop first
DROP VIEW IF EXISTS active_customers;
CREATE VIEW active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE active = 1;

Altering a view does not change dependent privileges or references, but if underlying columns are renamed or removed, the view may break until redefined.

Dropping views

DROP VIEW active_customers;

Dropping a view removes only its definition; it does not affect underlying tables or data.

Updatable vs read-only views

Some views can be updated directly with INSERT, UPDATE, or DELETE. Others are inherently read-only because their definition makes it ambiguous which base rows should change.

Updatable views

Views are generally updatable if they meet these conditions:

• They reference a single base table.
• They include all non-nullable columns from the base table.
• They contain no GROUP BY, DISTINCT, aggregates, or set operations.

-- Updatable view
CREATE VIEW department_staff AS
SELECT employee_id, name, department_id
FROM employees;

-- Update through the view
UPDATE department_staff
SET department_id = 3
WHERE employee_id = 102;

The database propagates such updates directly to the base table.

Read-only views

Views that use joins, aggregates, or derived columns are read-only because updates cannot be unambiguously mapped back to base data.

-- Read-only view
CREATE VIEW department_summary AS
SELECT d.department_name,
       COUNT(e.employee_id) AS staff_count
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_name;

Attempting to insert or update through such a view usually causes an error.

Enforcing rules with WITH CHECK OPTION

WITH CHECK OPTION ensures that inserts or updates through a view do not produce rows that fall outside the view’s WHERE condition.

CREATE VIEW active_employees AS
SELECT employee_id, name, department_id
FROM employees
WHERE active = 1
WITH CHECK OPTION;

If a user tries to insert an inactive employee through this view, the database rejects the operation because it violates the condition.

Materialized views

A materialized view stores the results of a query physically, unlike a regular view which executes the query each time. This improves performance for expensive aggregations or joins but requires refresh management to stay up to date.

Creating a materialized view

-- PostgreSQL and Oracle
CREATE MATERIALIZED VIEW sales_summary AS
SELECT region_id,
       SUM(total_amount) AS total_sales,
       COUNT(order_id) AS num_orders
FROM orders
GROUP BY region_id;

After creation, querying the materialized view reads from the stored result instead of recomputing the query.

Refreshing materialized views

Because data changes over time, materialized views must be refreshed manually or automatically.

-- Manual refresh (PostgreSQL)
REFRESH MATERIALIZED VIEW sales_summary;

-- Oracle supports ON COMMIT refresh
CREATE MATERIALIZED VIEW sales_summary
REFRESH FAST ON COMMIT AS
SELECT region_id, SUM(total_amount) AS total_sales
FROM orders
GROUP BY region_id;

Fast refresh updates only changed rows if the engine supports materialized view logs. Full refresh rebuilds the entire dataset.

Emulating materialized views

Engines that lack native materialized views (like MySQL before 8.0 or SQLite) can emulate them with a normal table and a scheduled job that repopulates it.

CREATE TABLE sales_summary AS
SELECT region_id,
       SUM(total_amount) AS total_sales
FROM orders
GROUP BY region_id;

-- Later: rebuild periodically
TRUNCATE TABLE sales_summary;
INSERT INTO sales_summary
SELECT region_id,
       SUM(total_amount)
FROM orders
GROUP BY region_id;

This approach gives similar performance benefits, though it requires external logic for consistency.

Caching strategies

Query results and materialized data can be cached at several layers to improve performance. The best strategy depends on data volatility and application workload.

Database-level caching

Most engines cache query execution plans and recently accessed data pages automatically. Tuning memory buffers and cache size parameters can greatly improve performance.

• MySQL: innodb_buffer_pool_size controls caching of table data and indexes.
• PostgreSQL: shared_buffers and work_mem influence cache behavior.
• SQL Server: uses automatic plan and page caching.

Application-level caching

Applications can store query results in memory (for example, Redis or Memcached) or local caches to avoid repeated database reads. This is effective for rarely changing data such as configuration lists or product catalogs.

# Example (Python pseudocode)
key = "sales_summary_2025"
data = cache.get(key)
if not data:
    data = db.query("SELECT * FROM sales_summary")
    cache.set(key, data, ttl=3600)
return data

Hybrid caching with materialized views

Combining materialized views with in-memory caching can balance freshness and performance. The database maintains semi-fresh summaries, and the application caches them briefly to reduce load further.

For example, a dashboard might read from a refreshed hourly materialized view, caching the result in memory for only a few minutes.

Monitoring and invalidation

Effective caching requires monitoring to ensure accuracy and performance. Invalidate or refresh caches when base data changes significantly. Many frameworks provide cache invalidation hooks tied to database triggers or data pipelines.

Views, materialized views, and caching layers together let you balance speed and accuracy. Used well, they simplify queries and reduce computation costs while maintaining a consistent, reliable interface to your data.