SQL Views

A view is a virtual table defined by a stored SQL query. It does not hold data itself — each time a view is referenced, the underlying query executes against the base tables. Views provide query abstraction, security (exposing only specific columns/rows), and schema stability (applications reference the view, insulated from underlying table changes).

Creating Views

CREATE VIEW active_employees AS
SELECT e.id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.status = 'active';

Once created, query it like a table:

SELECT * FROM active_employees WHERE department_name = 'Engineering';

Use Cases

• Security — Restrict access to sensitive columns (e.g., salary) by granting access to the view, not the base table. Views can also redact data:

  -- Non-marketing personnel see masked emails
  CREATE VIEW AuthorNM (fullname, email) AS
  SELECT name, substr(email,1,2) || '****' || substr(email,-5)
  FROM Author;

• Schema abstraction — A well-designed database exposes data only through views, never allowing direct table access. If a table or column is renamed, update the view definition — client queries against the view continue unchanged.
• Simplification — Encapsulate complex joins and subqueries behind a simple name
• Reporting — Pre-define analytical queries for business users
• Row-level filtering — Expose only rows a user should see

Updatable Views

Some views can be written to (INSERT, UPDATE, DELETE) if they meet conditions:

• Based on a single table
• No DISTINCT, GROUP BY, HAVING, aggregate functions, or subqueries
• No computed/derived columns
• Include all NOT NULL columns without defaults

-- This view is updatable
CREATE VIEW engineering_employees AS
SELECT id, name, email FROM employees WHERE dept_id = 3;
 
-- This insert goes to the base table
INSERT INTO engineering_employees (id, name, email) VALUES (101, 'Alice', 'alice@co.com');

WITH CHECK OPTION

Prevents inserts/updates through a view that would make the row invisible to the view:

CREATE VIEW engineering_employees AS
SELECT * FROM employees WHERE dept_id = 3
WITH CHECK OPTION;
 
-- This would fail: dept_id = 5 doesn't match WHERE dept_id = 3
UPDATE engineering_employees SET dept_id = 5 WHERE id = 101;

Materialized Views

Unlike regular views, materialized views physically store the query result and must be explicitly refreshed. Not available in SQLite, but supported in PostgreSQL, Oracle, and SQL Server.

-- PostgreSQL
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', sale_date) AS month, SUM(amount) AS total
FROM sales
GROUP BY 1;
 
REFRESH MATERIALIZED VIEW monthly_sales;

Trade-off: Faster reads (no re-execution), but stale data between refreshes.

Dropping Views

DROP VIEW IF EXISTS active_employees;

Views vs CTEs vs Subqueries

Feature	View	Common Table Expression (CTE)	Subquery
Persistence	Stored in schema	Query-scoped	Inline
Reusability	Across queries	Within one query	Single use
Security	Can grant access	No	No
Performance	Re-executed (unless materialized)	Re-executed	Re-executed

Sources

• Lecture-30-70.108 Query and Schema Abstraction with Views