SQL Database Objects

SQL Views

Save a query as a virtual table — simplify complex joins and secure sensitive data.

Simplify — Hide complex joins and subqueries behind a clean interface
Secure — Restrict access to specific columns or rows
Standardize — Provide a consistent API even when the underlying schema changes A view is a named query stored in the database — not a new copy of the data.

What Is a View?

DfView

A view is a stored SELECT statement that behaves like a virtual table. When you query a view, the database executes the underlying query and returns the results. Views do not store data — they execute their query on demand (unless materialized).

-- Create a view that joins employees with departments
CREATE VIEW v_employee_directory AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.email,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Query the view like a regular table
SELECT * FROM v_employee_directory
WHERE department_name = 'Engineering';

Simple vs Complex Views

DfSimple View

A simple view is derived from a single table and does not use aggregate functions. Most databases allow INSERT, UPDATE, and DELETE operations on simple views.

DfComplex View

A complex view is constructed from multiple tables, uses JOINs, GROUP BY, or aggregate functions. Most databases make complex views read-only.

Characteristic	Simple View	Complex View
Source tables	Single table	Multiple tables
JOINs	No	Yes
GROUP BY	No	Yes
Aggregate functions	No	Yes
DML (INSERT/UPDATE/DELETE)	Usually yes	Usually no
WITH CHECK OPTION	Supported	Limited

Creating Views

Basic View

CREATE VIEW v_active_employees AS
SELECT
    employee_id,
    first_name,
    last_name,
    email,
    department_id
FROM employees
WHERE status = 'Active';

View with JOIN

CREATE VIEW v_employee_compensation AS
SELECT
    e.employee_id,
    e.first_name || ' ' || e.last_name AS full_name,
    e.salary,
    COALESCE(b.bonus, 0) AS bonus,
    e.salary + COALESCE(b.bonus, 0) AS total_compensation
FROM employees e
LEFT JOIN bonuses b ON e.employee_id = b.employee_id;

View with Aggregation

CREATE VIEW v_department_summary AS
SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

View with Subquery

CREATE VIEW v_above_average_salary AS
SELECT
    employee_id,
    first_name,
    last_name,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Modifying and Dropping Views

-- Replace an existing view
CREATE OR REPLACE VIEW v_employee_directory AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.email,
    e.phone,
    d.department_name,
    e.salary,
    e.hire_date
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Drop a view
DROP VIEW IF EXISTS v_employee_directory;

-- Rename a view (PostgreSQL)
ALTER VIEW v_employee_directory RENAME TO v_employee_info;

Updatable Views

DfUpdatable View

A view is updatable when the database can map DML operations back to the underlying base table. Simple views on a single table are generally updatable. Views with JOINs, DISTINCT, GROUP BY, or aggregates are typically read-only.

-- Updatable view
CREATE VIEW v_senior_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary >= 80000;

-- This UPDATE flows through to the base table
UPDATE v_senior_employees
SET salary = salary * 1.10
WHERE employee_id = 101;

-- WITH CHECK OPTION prevents inserting rows that violate the view's WHERE clause
CREATE VIEW v_active_engineers AS
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 5 AND status = 'Active'
WITH CHECK OPTION;

-- This INSERT is rejected — violates the WHERE clause
INSERT INTO v_active_engineers (first_name, last_name, department_id)
VALUES ('Jane', 'Doe', 3);  -- ERROR: department_id is not 5

Materialized Views

DfMaterialized View

A materialized view stores the result of the query physically on disk. It must be refreshed explicitly or on a schedule to stay current. Materialized views are ideal for expensive aggregations and reporting queries.

-- PostgreSQL: Create a materialized view
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT
    product_id,
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount) AS total_sales,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY product_id, DATE_TRUNC('month', sale_date);

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW mv_sales_summary;

-- Concurrent refresh (PostgreSQL only — allows reads during refresh)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary;

Feature	Regular View	Materialized View
Data storage	No (virtual)	Yes (physical)
Query performance	Executes on demand	Pre-computed, instant
Data freshness	Always current	Stale until refreshed
Refresh needed	No	Yes
Storage cost	None	Disk space required
DML operations	Sometimes	Rarely

Views for Security

-- Hide sensitive salary data
CREATE VIEW v_public_employee_info AS
SELECT
    employee_id,
    first_name,
    last_name,
    email,
    department_id
FROM employees;

-- Grant access only to the view, not the base table
GRANT SELECT ON v_public_employee_info TO public_user;
REVOKE SELECT ON employees FROM public_user;

-- Row-level security via views
CREATE VIEW v_my_team AS
SELECT *
FROM employees
WHERE manager_id = CURRENT_USER_ID();

Performance Considerations

Scenario	Recommendation
Simple column selection from a single table	Use a view for abstraction, not performance
Complex JOIN across many tables	View helps readability; optimize the underlying query
Expensive aggregation run frequently	Use a materialized view
Dashboard with fixed time ranges	Materialized view refreshed nightly
Real-time data requirements	Regular view (not materialized)

Views do not improve performance on their own — they execute the underlying query each time. For expensive, frequently-run queries, use materialized views instead.

Common Pitfalls

Pitfall	Problem	Solution
Nested views (view of a view)	Hard to debug, performance degrades	Keep views shallow — one level deep
Overusing views for everything	Creates a maze of dependencies	Use views selectively for complex joins and security
Forgetting to refresh materialized views	Stale data served to users	Schedule refreshes or use triggers
Updatable views with complex joins	DML operations fail silently	Test DML on views before relying on them
Naming inconsistency	Confusion between tables and views	Prefix views with `v_` or `vw_`

Key Takeaways

A view is a stored SELECT statement — a virtual table that executes its query on demand
Simple views on single tables are often updatable; complex views with JOINs are usually read-only
Materialized views store results physically and must be refreshed — ideal for expensive aggregations
Views are powerful for security — they can hide columns, rows, or sensitive data from users
Views do not improve performance by themselves; use materialized views for pre-computed results

SQL Views

SQL Views

What Is a View?

DfView

Simple vs Complex Views

DfSimple View

DfComplex View

Creating Views

Basic View

View with JOIN

View with Aggregation

View with Subquery

Modifying and Dropping Views

Updatable Views

DfUpdatable View

Materialized Views

DfMaterialized View

Views for Security

Performance Considerations

Common Pitfalls

Key Takeaways

Premium Content

Need Expert SQL Help?