Views in SQL

A view is a saved SQL query that behaves like a virtual table. You query a view exactly as you would a table, but it stores no data of its own — it runs its underlying SELECT each time you use it. Views let you simplify complex queries, present a cleaner interface to users, and restrict which columns or rows people can see.

Think of a view as a named, reusable window onto your data.

Creating and using a view

You define a view with CREATE VIEW, then query it like any table:

-- PostgreSQL / MySQL compatible

-- Define a view that hides the join complexity
CREATE VIEW active_enrolments AS
SELECT
    s.student_id,
    s.full_name,
    e.course,
    e.enrolled_on
FROM student s
JOIN enrolment e ON s.student_id = e.student_id
WHERE e.status = 'active';

-- Now query it like a table — the join is hidden away
SELECT full_name, course
FROM active_enrolments
WHERE course = 'DBMS';

Anyone using active_enrolments does not need to know about the join or the status = 'active' filter — the view encapsulates that logic in one place.

Why use views?

• Simplicity. Wrap a complicated multi-table join once; reuse it everywhere with a simple name.
• Consistency. Everyone uses the same definition of "active enrolment," so reports agree.
• Security. Grant access to a view that exposes only certain columns/rows, while keeping the base table private. A view over student that omits phone and address lets analysts query names without seeing personal data.
• Abstraction. If the underlying tables change, you can often update the view definition and leave the queries that use it untouched.

Regular (virtual) views vs materialized views

There are two kinds, and the difference is fundamental:

• A regular view stores no data. Every query against it re-runs the underlying SELECT against live tables — so results are always current, but a slow query stays slow each time.
• A materialized view stores the result on disk, like a cached snapshot. Reads are fast, but the data is only as fresh as the last refresh.

-- PostgreSQL: materialized view caches the result
CREATE MATERIALIZED VIEW enrolment_summary AS
SELECT course, COUNT(*) AS total
FROM enrolment
GROUP BY course;

-- You must refresh it to pick up new data
REFRESH MATERIALIZED VIEW enrolment_summary;

Vendor note: PostgreSQL supports materialized views natively. MySQL does not have a built-in materialized view feature — people emulate it with a regular table plus scheduled refresh logic. This is an important portability difference. Regular CREATE VIEW works in both.

Use a materialized view when the underlying query is expensive and slightly stale data is acceptable (e.g. a nightly dashboard). Use a regular view when freshness matters and the query is cheap.

Are views updatable?

Sometimes you can INSERT, UPDATE, or DELETE through a view, and the change flows to the base table. This works only for simple views — typically those built on a single table without aggregation, DISTINCT, GROUP BY, or joins.

-- A simple, updatable view over one table
CREATE VIEW jalgaon_students AS
SELECT student_id, full_name, city
FROM student
WHERE city = 'Jalgaon';

-- This UPDATE flows through to the student table
UPDATE jalgaon_students
SET full_name = 'Asha S. Patil'
WHERE student_id = 1;

Views containing joins, GROUP BY, or aggregates are generally read-only — the database cannot unambiguously decide which base row to change. For those, use INSTEAD OF triggers (advanced) or update the base tables directly.

You can also add WITH CHECK OPTION so that inserts/updates through the view cannot create rows that fall outside the view's WHERE condition:

CREATE VIEW jalgaon_students AS
SELECT student_id, full_name, city
FROM student
WHERE city = 'Jalgaon'
WITH CHECK OPTION;   -- blocks setting city to anything but 'Jalgaon' via this view

Dropping and replacing views

-- Replace a view's definition (PostgreSQL & MySQL)
CREATE OR REPLACE VIEW active_enrolments AS
SELECT student_id, full_name, course FROM ... ;

-- Remove a view entirely
DROP VIEW active_enrolments;

Dropping a view never affects the underlying data — only the saved query definition.

Common mistakes

• Expecting a regular view to be fast. A view does not cache anything; if its query is slow, every read is slow. For caching, use a materialized view (PostgreSQL) or a refreshed table.
• Trying to update a complex view. Views with joins or aggregation are usually read-only. Update the base tables instead.
• Forgetting materialized views go stale. They show data as of the last REFRESH. Schedule refreshes or you will serve outdated results.
• Assuming materialized views exist in MySQL. They do not natively. Plan an alternative if you need portability.
• Over-nesting views. A view built on a view built on a view becomes hard to debug and can hide performance problems. Keep the layering shallow.

FAQ

Does a view take up storage? A regular view does not — only its definition is stored. A materialized view does, because it holds the cached result set.

Can a view improve security? Yes. Granting access to a view that exposes only safe columns/rows, while restricting the base table, is a common and effective access-control pattern.

Are views slower than querying tables directly? A regular view runs the same query you would have written, so performance is essentially identical. It is the underlying query's cost, not the view wrapper, that matters.

Keep learning

Views build on SQL joins and aggregate functions, and they pair well with constraints in SQL for clean, safe data access.

Want to design views that simplify real applications? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.