What Is a View?

-- 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

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

-- 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

-- 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;

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

Common Pitfalls