intermediate

Views in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

Overview

A VIEW in SQL is a virtual table defined by a saved SQL query. Unlike physical tables, a standard view does not store data on disk; instead, whenever you query a view, the database engine dynamically evaluates the underlying SELECT statement and merges its execution plan with your query. Views serve as powerful architectural abstractions: they simplify complex queries, encapsulate business logic, provide security access boundaries, and hide sensitive columns from unauthorized users.

What You Will Learn

In this lesson, you will master:
  • Virtual Views vs Tables: How views expand dynamically into query plans.
  • Security & Data Masking: Exposing subset columns to restricted database roles without duplicating data.
  • Updatable Views: The strict rules governing when INSERT, UPDATE, and DELETE can be run against a view.
  • WITH CHECK OPTION: Preventing updates through views that would violate the view's own filtering predicate.
  • Materialized Views: Caching query results on disk and scheduling refresh cycles for analytical reporting.

Defining & Querying Standard Views

Consider a multi-table database with customers, orders, and order items. Querying revenue per customer requires repeated complex joins:

sql
-- Create a reusable business view
CREATE VIEW vw_customer_order_summary AS
SELECT 
    c.customer_id,
    c.full_name,
    c.email,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COALESCE(SUM(o.order_total), 0.0) AS lifetime_spend,
    MAX(o.ordered_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.full_name, c.email;

-- Now downstream analysts can query it like a simple table!
SELECT * 
FROM vw_customer_order_summary 
WHERE lifetime_spend > 5000 
ORDER BY lifetime_spend DESC;

Column Masking & Security Encapsulation

Views are an industry-standard mechanism for enforcing Least Privilege access:

sql
-- Sensitive Base Table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    ssn VARCHAR(11) NOT NULL,            -- SENSITIVE PII
    base_salary NUMERIC(10, 2) NOT NULL, -- SENSITIVE COMPENSATION
    department VARCHAR(50)
);

-- Public Directory View: Hides SSN and Salary
CREATE VIEW vw_public_employee_directory AS
SELECT 
    employee_id,
    first_name,
    last_name,
    department
FROM employees;

-- Grant permissions ONLY to the view, not the base table
GRANT SELECT ON vw_public_employee_directory TO junior_analyst_role;
REVOKE SELECT ON employees FROM junior_analyst_role;

Updatable Views & WITH CHECK OPTION

A view can accept UPDATE or INSERT statements only if it meets strict criteria:

  1. It references exactly one base table in the FROM clause.
  2. It does not contain GROUP BY, HAVING, DISTINCT, aggregate functions, or UNION.
  3. All NOT NULL columns without default values from the base table are present in the view.
sql
CREATE VIEW vw_active_users AS
SELECT user_id, username, email, is_active
FROM users
WHERE is_active = TRUE
WITH CHECK OPTION; -- Critical integrity clause!

Why WITH CHECK OPTION Matters:

Without WITH CHECK OPTION, a user executing:

sql
UPDATE vw_active_users SET is_active = FALSE WHERE user_id = 10;

would succeed, immediately causing user 10 to "disappear" from the view! With WITH CHECK OPTION, the database rejects any write that would make the modified row fall outside the view's WHERE filter.


Standard Views vs Materialized Views

| Feature | Standard Virtual View | Materialized View | | :--- | :--- | :--- | | Storage | 0 bytes on disk (Just query definition text) | Physical disk storage (Stores query result table) | | Compute Cost | Evaluated dynamically on every read | Evaluated once at refresh time | | Read Speed | Dependent on underlying table performance | Instantaneous disk read ($O(1)$ / $O(\log N)$ with indexes) | | Data Freshness | 100% Real-time / Live data | As fresh as the last refresh timestamp | | Indexing | Cannot create indexes on standard views | Can create secondary B-Tree indexes! |

sql
-- Creating a Materialized View in PostgreSQL
CREATE MATERIALIZED VIEW mv_daily_sales_rollup AS
SELECT 
    DATE(ordered_at) AS sale_date,
    product_id,
    COUNT(*) AS total_sales,
    SUM(order_total) AS total_revenue
FROM orders
GROUP BY DATE(ordered_at), product_id;

-- Create an index directly on the materialized view
CREATE INDEX idx_mv_sales_date ON mv_daily_sales_rollup(sale_date);

-- Refreshing the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales_rollup;

Common Mistakes & Gotchas

  • View Over View (Nesting Inefficiency): Building views on top of views (5 levels deep) causes query plan explosion. The optimizer struggles to push predicates through deeply nested join views, resulting in suboptimal execution plans and slow queries.
  • Assuming Views Cache Data: Standard views do not cache data. If the underlying query takes 30 seconds, querying SELECT * FROM my_view will take 30 seconds every single time. Use Materialized Views for expensive aggregation caching.

Interview Perspective

SQL Interview Question: When would you use a Materialized View instead of a standard View, and what is the trade-off?

Key Talking Points for Candidates:

  1. Standard Views: Ideal for abstraction, security masking, and query simplification where underlying data changes frequently and real-time consistency is mandatory.
  2. Materialized Views: Ideal for heavy analytics, complex multi-table joins, and aggregate dashboards where computing results on the fly is too slow.
  3. Trade-offs: Materialized views consume physical storage, require automated refresh pipelines (REFRESH MATERIALIZED VIEW), and serve slightly stale data between refreshes.

Interactive Practice Challenge

Challenge: Secure Active Vendor View

Create a view called vw_active_vendors that exposes vendor_id, vendor_name, and support_email from an existing vendors table, filtered to only vendors where status = 'ACTIVE'. Ensure that no one can update a vendor through this view to have a status other than 'ACTIVE'.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.