Eric Campbell 6 min read Databases

SQL Views: Virtual Tables and Materialized Views

SQL views are named queries stored in your database that act as virtual tables. Unlike physical tables, standard views don't store data—they're essentially saved SELECT statements that execute...

Key Insights

  • Standard views are stored queries that execute on-demand, adding no storage overhead but potentially impacting performance for complex queries, while materialized views physically store result sets and require explicit refreshes but deliver significantly faster query times.
  • Views provide critical abstraction layers for security, simplifying complex joins and calculations, but updatable views only work with simple queries—complex aggregations or multi-table joins require INSTEAD OF triggers.
  • Materialized views excel for reporting and analytics on slowly-changing data, but the refresh strategy (manual, scheduled, or on-commit) directly impacts data freshness and system load, making them unsuitable for real-time transactional workloads.

Introduction to SQL Views

SQL views are named queries stored in your database that act as virtual tables. Unlike physical tables, standard views don’t store data—they’re essentially saved SELECT statements that execute whenever you query them. This abstraction layer sits between your application and the underlying tables, providing a simplified interface to complex data structures.

Views serve multiple purposes: they encapsulate business logic, enforce security policies, maintain backward compatibility when schemas change, and make complex queries reusable. Instead of writing the same 50-line JOIN statement across multiple application endpoints, you write it once as a view and query it like a table.

Here’s the basic syntax:

CREATE VIEW active_customers AS
SELECT 
    customer_id,
    first_name,
    last_name,
    email,
    registration_date
FROM customers
WHERE status = 'active' 
    AND last_login > CURRENT_DATE - INTERVAL '90 days';

Now active_customers behaves like a table. Query it with SELECT * FROM active_customers and the database executes the underlying SELECT statement, filtering results in real-time.

Standard (Virtual) Views

Standard views are query macros. When you SELECT from a view, the database substitutes the view definition into your query and executes the combined statement. This happens every time—there’s no caching, no stored results.

Consider this real-world scenario where you need customer order summaries:

CREATE VIEW customer_order_summary AS
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS lifetime_value,
    MAX(o.order_date) AS last_order_date,
    AVG(o.total_amount) AS average_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id, c.first_name, c.last_name, c.email;

When you query this view:

SELECT * FROM customer_order_summary 
WHERE lifetime_value > 1000
ORDER BY lifetime_value DESC
LIMIT 10;

The database merges both queries and executes:

SELECT 
    c.customer_id,
    c.first_name,
    -- ... all columns
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
HAVING SUM(o.total_amount) > 1000
ORDER BY SUM(o.total_amount) DESC
LIMIT 10;

The query optimizer can push down predicates and optimize the combined query, but you’re still executing the full JOIN and aggregation every time. For tables with millions of rows, this gets expensive fast.

Materialized Views

Materialized views solve the performance problem by physically storing query results. They’re actual tables that the database populates and maintains based on a query definition. The trade-off is simple: you get fast queries but stale data until you refresh.

PostgreSQL syntax:

CREATE MATERIALIZED VIEW product_sales_summary AS
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    COUNT(DISTINCT oi.order_id) AS order_count,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    AVG(oi.unit_price) AS average_price,
    MAX(o.order_date) AS last_sold_date
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY p.product_id, p.product_name, p.category;

This creates a physical table with the aggregated results. Queries against this materialized view hit stored data, not the underlying tables:

-- Lightning fast - no joins, no aggregations
SELECT * FROM product_sales_summary
WHERE category = 'Electronics'
ORDER BY total_revenue DESC;

But the data becomes stale immediately. You must explicitly refresh:

-- PostgreSQL
REFRESH MATERIALIZED VIEW product_sales_summary;

-- Oracle - manual refresh
EXEC DBMS_MVIEW.REFRESH('product_sales_summary');

-- Oracle - automatic refresh on commit (expensive!)
CREATE MATERIALIZED VIEW product_sales_summary
REFRESH FAST ON COMMIT AS
SELECT ...;

Refresh strategies matter enormously. Manual refresh gives you control—run it nightly via cron when system load is low. On-commit refresh keeps data synchronized but adds overhead to every transaction touching base tables. Scheduled refresh (Oracle’s REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1/24) balances freshness and performance.

Performance Comparison

The performance difference is dramatic. Here’s what EXPLAIN ANALYZE shows for a complex reporting query:

-- Direct query against base tables
EXPLAIN ANALYZE
SELECT category, SUM(total_revenue) 
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
GROUP BY category;

-- Result: Execution time: 2847.392 ms
-- Hash Join -> Hash Join -> GroupAggregate
-- Scans: 1.2M rows from orders, 3.4M from order_items

Same query against a materialized view:

EXPLAIN ANALYZE
SELECT category, SUM(total_revenue)
FROM product_sales_summary
GROUP BY category;

-- Result: Execution time: 23.156 ms
-- Sequential Scan -> HashAggregate
-- Scans: 15K rows (pre-aggregated)

That’s a 120x speedup. Standard views would show similar execution times to the direct query since they execute the underlying statement.

The trade-off matrix:

  • Standard views: Always fresh data, no storage cost, slow for complex queries
  • Materialized views: Stale data, storage cost, extremely fast queries
  • Direct tables: Full control, but no abstraction or security benefits

Updatable Views

Simple views over single tables are updatable in most databases:

CREATE VIEW active_users AS
SELECT user_id, username, email, status
FROM users
WHERE status = 'active';

-- This works
INSERT INTO active_users (username, email, status)
VALUES ('jdoe', 'john@example.com', 'active');

-- This also works
UPDATE active_users 
SET email = 'newemail@example.com'
WHERE user_id = 123;

The database translates these operations to the underlying users table. But add a JOIN or aggregation and updates fail:

-- This view is NOT updatable
CREATE VIEW user_order_counts AS
SELECT u.user_id, u.username, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;

-- This fails with an error
UPDATE user_order_counts SET username = 'newname' WHERE user_id = 1;

For complex views, use INSTEAD OF triggers:

CREATE TRIGGER update_user_order_counts
INSTEAD OF UPDATE ON user_order_counts
FOR EACH ROW
BEGIN
    UPDATE users 
    SET username = NEW.username
    WHERE user_id = NEW.user_id;
END;

Now updates work, but you’re writing manual logic to translate view operations into base table modifications. This gets complex quickly—most teams avoid updatable complex views entirely.

Best Practices and Common Pitfalls

Use views for security and data masking:

CREATE VIEW customer_support_view AS
SELECT 
    customer_id,
    first_name,
    last_name,
    CONCAT(SUBSTRING(email, 1, 3), '***@***') AS masked_email,
    'XXX-XX-' || RIGHT(ssn, 4) AS masked_ssn,
    account_status
FROM customers;

-- Grant access only to the view
GRANT SELECT ON customer_support_view TO support_team;
REVOKE ALL ON customers FROM support_team;

Naming conventions matter. Prefix views with v_ or vw_ to distinguish them from tables. For materialized views, use mv_ or mat_. Your future self will thank you.

Avoid view-on-view-on-view architectures. Each layer adds complexity and makes query optimization harder. Two levels maximum.

Monitor materialized view refresh times. A refresh that takes 4 hours is useless if you need hourly updates. Consider incremental refresh strategies or partitioning base tables.

Don’t use materialized views for transactional data. They’re for analytics and reporting on relatively stable data, not real-time inventory counts or account balances.

Database-Specific Features

SQL Server has indexed views (similar to materialized views but with automatic refresh):

CREATE VIEW dbo.OrderTotals
WITH SCHEMABINDING AS
SELECT 
    customer_id,
    COUNT_BIG(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM dbo.Orders
GROUP BY customer_id;

CREATE UNIQUE CLUSTERED INDEX idx_OrderTotals 
ON OrderTotals(customer_id);

The SCHEMABINDING prevents schema changes to underlying tables. SQL Server maintains the index automatically—no manual refresh needed, but every INSERT/UPDATE/DELETE on Orders updates the view.

PostgreSQL materialized views support CONCURRENTLY refresh:

REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales_summary;

This allows queries during refresh (requires a unique index), preventing downtime for large refreshes.

MySQL only supports standard views—no materialized views. You must manually create and maintain summary tables.

Oracle offers the most sophisticated materialized view features: query rewrite (automatically uses materialized views even when queries target base tables), fast refresh using materialized view logs, and partition change tracking.

Choose your view strategy based on data volatility, query patterns, and acceptable staleness. Standard views for frequently-changing transactional data, materialized views for analytics and reporting, and indexed views (SQL Server) when you need both freshness and performance.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.