SQL Views — Simplifying Queries, Controlling Access, and Materialized Views

What a view is

A view is a named SELECT statement stored in the database. It looks and behaves like a table from the caller's perspective but contains no data of its own — every query against a view re-executes the underlying SELECT against the base tables (unless it is a materialized view).

Views serve three purposes: simplify complex queries into a reusable name, control access by exposing only certain columns or rows, and decouple application code from the underlying schema.

Creating and using views

-- A view that joins orders with customer details
CREATE OR REPLACE VIEW order_summary AS
SELECT
    o.id                    AS order_id,
    c.email                 AS customer_email,
    c.display_name          AS customer_name,
    o.status,
    o.total_amount,
    o.created_at
FROM orders    o
JOIN customers c ON c.id = o.customer_id;

-- Use it like a table
SELECT * FROM order_summary WHERE status = 'pending' ORDER BY created_at DESC;
SELECT customer_email, COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM   order_summary
GROUP  BY customer_email;

CREATE OR REPLACE VIEW updates the view definition without dropping it, so existing permissions on the view are preserved.

Views for access control

Grant access to a view but not the underlying tables to restrict what users can see:

-- Base table has sensitive columns (salary, SSN)
-- View exposes only what HR partners need
CREATE VIEW employee_directory AS
SELECT id, full_name, department, job_title, hire_date
FROM   employees
WHERE  terminated_at IS NULL;

GRANT SELECT ON employee_directory TO hr_partner_role;
REVOKE ALL ON employees FROM hr_partner_role;
-- HR partners can query employee_directory but not employees directly

Row-level filtering in views is an alternative to row-level security policies:

-- Each sales rep can only see their own assigned leads
CREATE VIEW my_leads AS
SELECT * FROM leads
WHERE assigned_to = current_user;

Updatable views

A view is updatable (INSERT/UPDATE/DELETE pass through to the base table) if it meets specific criteria: single base table, no DISTINCT, GROUP BY, HAVING, UNION, aggregate functions, or window functions.

-- Simple updatable view
CREATE VIEW active_products AS
SELECT id, product_name, unit_price, stock_qty
FROM   products
WHERE  status = 'active';

-- This INSERT goes to the products table
INSERT INTO active_products (product_name, unit_price, stock_qty)
VALUES ('USB-C Hub', 29.99, 500);

-- This UPDATE modifies the base table row
UPDATE active_products SET unit_price = 34.99 WHERE id = 77;

For complex views that cannot be updatable automatically, use INSTEAD OF triggers (SQL Server) or rewrite rules (Postgres) to define custom write behaviour.

Materialized views — cached query results

A materialized view stores the result set physically. Queries against it do not re-execute the underlying SELECT — they read the cached data. This is a major performance win for expensive aggregations and joins.

-- Postgres: create a materialized view of the daily sales summary
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE(o.created_at)               AS sale_date,
    p.category,
    COUNT(DISTINCT o.id)             AS order_count,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders      o
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
WHERE o.status = 'completed'
GROUP BY DATE(o.created_at), p.category
WITH DATA;  -- populate immediately (use WITH NO DATA to defer)

-- Query the cached result (no re-execution of the underlying query)
SELECT * FROM daily_sales_summary WHERE sale_date >= '2026-06-01';

-- Refresh when source data changes
REFRESH MATERIALIZED VIEW daily_sales_summary;

-- Refresh without locking reads (Postgres, requires a UNIQUE index)
CREATE UNIQUE INDEX ON daily_sales_summary (sale_date, category);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

View vs CTE vs temp table vs materialized view

Recursive views (Postgres)

CREATE RECURSIVE VIEW category_tree (id, name, parent_id, depth) AS
    SELECT id, name, parent_id, 0
    FROM   categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM   categories c JOIN category_tree ct ON c.parent_id = ct.id;

Recap

Regular views simplify complex queries and enforce access control — they are always up-to-date but re-execute every time. Materialized views sacrifice currency for performance — schedule REFRESH to match your staleness tolerance. Use CONCURRENTLY refresh on Postgres (requires a unique index) to avoid blocking readers during the refresh. Keep views updatable by avoiding aggregates and DISTINCT; use INSTEAD OF triggers for complex update paths.