Views & Virtual Tables

What Is a View?

A view is a named, saved SQL query stored in the database. It looks and acts like a table — you can SELECT from it, join it, filter it — but it doesn't store data itself. Each time you query a view, the underlying query runs against the actual tables.

-- Create a view
CREATE VIEW high_earners AS
SELECT name, department, salary
FROM employees
WHERE salary > 80000;

-- Use the view like a table
SELECT * FROM high_earners;
SELECT name FROM high_earners WHERE department = 'Finance';

The view is a saved lens — every time you query it, you're really running the original SELECT ... WHERE salary > 80000 against the employees table.

Creating Views

Syntax

CREATE VIEW view_name AS
SELECT ...;

-- With IF NOT EXISTS (SQLite / MySQL)
CREATE VIEW IF NOT EXISTS view_name AS
SELECT ...;

-- Replace existing view (PostgreSQL / MySQL)
CREATE OR REPLACE VIEW view_name AS
SELECT ...;

Simple View

CREATE VIEW finance_team AS
SELECT id, name, salary, hire_date
FROM employees
WHERE department = 'Finance';

-- Query the view
SELECT * FROM finance_team ORDER BY salary DESC;
SELECT COUNT(*) FROM finance_team;

View with Join

CREATE VIEW employee_details AS
SELECT
    e.id,
    e.name                AS employee_name,
    d.name                AS department,
    e.salary,
    e.hire_date,
    m.name                AS manager_name
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN employees m ON e.manager_id = m.id;

-- Use the view in a query
SELECT employee_name, department, salary
FROM employee_details
WHERE salary > 75000
ORDER BY department, salary DESC;

View with Aggregation

CREATE VIEW dept_summary AS
SELECT
    d.name                        AS department,
    COUNT(e.id)                   AS headcount,
    ROUND(AVG(e.salary), 0)       AS avg_salary,
    SUM(e.salary)                 AS total_payroll,
    MIN(e.hire_date)              AS first_hire,
    MAX(e.hire_date)              AS latest_hire
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

-- Simple to query:
SELECT department, headcount, avg_salary
FROM dept_summary
ORDER BY avg_salary DESC;

Why Use Views?

1. Simplify Complex Queries

Without a view, every developer must repeat a complex join:

-- Without view (repeated everywhere)
SELECT e.name, d.name AS dept, m.name AS manager
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN employees m ON e.manager_id = m.id;

-- With view (write once, use anywhere)
SELECT name, dept, manager FROM employee_details;

2. Data Abstraction

Views decouple application code from table structure. If you rename a column or split a table, update the view — application queries stay unchanged.

-- Application queries: SELECT full_name FROM people_view
-- Underlying table changed its column from 'name' to 'full_name'
-- Just update the view — app code unchanged

CREATE OR REPLACE VIEW people_view AS
SELECT full_name, email FROM users;  -- updated to match new column name

3. Row-Level Security

Restrict which rows different users can see:

-- HR team sees only their own department
CREATE VIEW my_department AS
SELECT * FROM employees
WHERE department = CURRENT_USER();  -- PostgreSQL

-- Or per-user visibility
CREATE VIEW employee_self AS
SELECT name, salary, department
FROM employees
WHERE email = CURRENT_USER;

4. Column-Level Security

Hide sensitive columns — show a view without salary or personal data:

CREATE VIEW employee_directory AS
SELECT id, name, department, email
FROM employees;
-- salary is NOT included — external systems query this view

GRANT SELECT ON employee_directory TO reporting_role;
-- reporting_role can query names/departments but never sees salary

Dropping Views

DROP VIEW IF EXISTS high_earners;

Dropping a view does not affect the underlying tables.

Updatable Views

In many databases, a simple view (no aggregation, no DISTINCT, no GROUP BY, single table, no UNION) is updatable — you can run INSERT, UPDATE, DELETE through it:

-- Simple view on one table
CREATE VIEW finance_team AS
SELECT id, name, salary FROM employees WHERE department = 'Finance';

-- UPDATE through the view
UPDATE finance_team SET salary = salary * 1.10 WHERE name = 'Priya Sharma';
-- This modifies the employees table

-- INSERT through the view (department must have a default or be nullable)
INSERT INTO finance_team (name, salary) VALUES ('New Person', 70000);

Rules for updatable views vary by database. Complex views (with joins, aggregations, GROUP BY, DISTINCT, UNION) are generally not updatable.

WITH CHECK OPTION

Prevents inserting/updating rows that would disappear from the view:

CREATE VIEW high_earners AS
SELECT name, salary FROM employees WHERE salary > 80000
WITH CHECK OPTION;

-- This would be rejected — 60000 < 80000, row wouldn't appear in view after insert
INSERT INTO high_earners (name, salary) VALUES ('Test', 60000);
-- Error: CHECK OPTION failed

Materialised Views (PostgreSQL)

A materialised view stores the query result physically — the data is cached:

-- PostgreSQL
CREATE MATERIALIZED VIEW dept_stats AS
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Query runs instantly (reads cached data, not base tables)
SELECT * FROM dept_stats;

-- Refresh when underlying data changes
REFRESH MATERIALIZED VIEW dept_stats;

-- Non-blocking refresh (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_stats;

Use materialised views for:

• Expensive aggregation queries run frequently (dashboards)
• Data that doesn't need to be real-time

Not supported in MySQL/SQLite — simulate with a regular table that you TRUNCATE and repopulate on a schedule.

Practical Examples

Example 1: Reporting View — Monthly Hires

CREATE VIEW monthly_hire_report AS
SELECT
    SUBSTR(hire_date, 1, 7)     AS month,
    department,
    COUNT(*)                    AS new_hires,
    ROUND(AVG(salary), 0)       AS avg_starting_salary
FROM employees
GROUP BY SUBSTR(hire_date, 1, 7), department
ORDER BY month DESC, department;

-- Query: hires in 2022
SELECT * FROM monthly_hire_report WHERE month LIKE '2022%';

Example 2: Active Employees with Derived Fields

CREATE VIEW active_employees AS
SELECT
    id,
    name,
    department,
    salary,
    ROUND(salary / 12, 2)           AS monthly_salary,
    hire_date,
    CASE
        WHEN salary >= 90000 THEN 'Senior'
        WHEN salary >= 75000 THEN 'Mid'
        ELSE 'Junior'
    END                             AS seniority
FROM employees
WHERE is_active = 1;

-- Dashboard query
SELECT department, seniority, COUNT(*) AS headcount
FROM active_employees
GROUP BY department, seniority
ORDER BY department, seniority;

Example 3: Security View for External Reporting

-- Full internal table
CREATE TABLE employees (
    id           INTEGER PRIMARY KEY,
    name         TEXT,
    email        TEXT,
    salary       REAL,
    ssn          TEXT,        -- sensitive
    bank_account TEXT,        -- sensitive
    department   TEXT
);

-- Safe view for external reporting system
CREATE VIEW employee_public AS
SELECT id, name, department
FROM employees;

-- Grant only this view to the reporting tool
GRANT SELECT ON employee_public TO reporting_user;

Common Mistakes

1. Expecting views to cache data (regular views)

-- Every SELECT on a view re-runs the full underlying query
-- If the underlying query is slow, the view is slow
SELECT * FROM complex_view;  -- runs the whole JOIN every time

-- Solution: use a materialised view (PostgreSQL) or scheduled cache table

2. Updating a view that isn't updatable

-- View with GROUP BY — not updatable
CREATE VIEW dept_avg AS
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;

UPDATE dept_avg SET avg_salary = 85000 WHERE department = 'Finance';
-- Error: view is not updatable

3. Dropping a view's base table

DROP TABLE employees;
-- Subsequent queries on views based on employees will fail

4. Not using OR REPLACE when updating views

-- Must drop and recreate if CREATE OR REPLACE isn't available (SQLite)
DROP VIEW IF EXISTS high_earners;
CREATE VIEW high_earners AS SELECT name, salary FROM employees WHERE salary > 85000;

Practice Exercises

1. Create a view called marketing_team that shows name, salary, and hire_date of all Marketing employees.
2. Create a view called payroll_summary that shows department, total salary, and headcount for each department.
3. Query the payroll_summary view to find departments with a total payroll over 200,000.
4. Create a view that shows each employee's name, their department name (via JOIN), and their monthly salary.
5. Add WITH CHECK OPTION to a view that filters salary > 70000. Try inserting a row with salary 60,000 and explain what happens.

Summary

In this chapter you learned:

• A view is a named, saved SQL query — not stored data, just a stored query
• CREATE VIEW name AS SELECT ... — define a view
• CREATE OR REPLACE VIEW — update an existing view
• DROP VIEW IF EXISTS name — remove a view
• Views simplify complex queries, enforce security, and provide data abstraction
• Updatable views (simple, single-table, no aggregation) support INSERT/UPDATE/DELETE
• WITH CHECK OPTION prevents updates that would make rows "disappear" from the view
• Materialised views (PostgreSQL) cache query results physically — refresh with REFRESH MATERIALIZED VIEW
• Always consider whether a slow view should be materialised

Next up: Stored Procedures & Functions — package reusable SQL logic in the database.