A view is a saved query that behaves like a table you never have to maintain. You name a SELECT once, and from then on you can query that name as if it were a real table, while the database quietly re-runs the underlying query every time.
Views are one of the most underused features in day to day SQL. They cost nothing to create, they hide complexity behind a friendly name, and they let you enforce consistent business logic across every report and application that touches your data. This guide explains exactly what a view is, how to create and drop one, when to reach for a view versus a materialized view, and where the sharp edges are around updates and performance. Every example is real, runnable SQL that works with small tweaks across MySQL, PostgreSQL, and SQL Server.
We use one small pair of tables throughout so you can follow every result by eye:
-- Two tables we will build views on top of
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(40),
country VARCHAR(20),
vip BOOLEAN
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_day DATE,
amount INT
);What this guide covers
1. What a view actually is
A view is a named, stored SELECT statement. It is often called a virtual table because you can query it exactly like a table, but it holds no data of its own. When you run a query against a view, the database expands the view definition into your query and executes the combined statement against the real underlying tables. The rows you see are always current, because they are computed on the spot.
Contrast that with a regular table, which physically stores rows on disk. A view stores only a definition. This single difference explains almost every property of views: they are always fresh, they take up almost no space, and they can be as fast or as slow as the query behind them. The formal definition in the SQL standard is worth a read if you want the precise semantics, and the Wikipedia article on SQL views is a concise overview of how different engines implement the concept.
| Property | Table | View |
|---|---|---|
| Stores rows on disk | Yes | No (just a query) |
| Data freshness | As written | Always live |
| Storage cost | Full data size | Near zero |
| Can be indexed directly | Yes | Usually no |
| Query cost | Direct read | Cost of the saved query |
Key idea: a view is a query wearing the mask of a table. Nothing is copied or cached (unless it is a materialized view, covered later), so a view is only as fast as the SELECT that defines it.
2. CREATE VIEW, CREATE OR REPLACE VIEW, and DROP VIEW
You define a view with CREATE VIEW followed by a name and the query that powers it. Here is a view that joins customers to their orders and labels each order by size:
CREATE VIEW customer_orders AS
SELECT c.name, c.country, o.order_day, o.amount,
CASE WHEN o.amount >= 500 THEN 'large'
ELSE 'small' END AS size_band
FROM customers c
JOIN orders o ON o.customer_id = c.id;From now on, customer_orders is queryable like any table. If you need to change the definition, do not drop and recreate it by hand. Use CREATE OR REPLACE VIEW, which swaps the definition in place while keeping the same name and permissions:
-- Redefine in place: add a running label, keep grants intact
CREATE OR REPLACE VIEW customer_orders AS
SELECT c.name, c.country, o.order_day, o.amount,
CASE WHEN o.amount >= 500 THEN 'large'
WHEN o.amount >= 200 THEN 'medium'
ELSE 'small' END AS size_band
FROM customers c
JOIN orders o ON o.customer_id = c.id;When you no longer need a view, remove it with DROP VIEW. Add IF EXISTS to avoid an error when the view is already gone, which keeps migration scripts idempotent:
DROP VIEW IF EXISTS customer_orders;One caveat on CREATE OR REPLACE: in most engines you cannot change the column list or order in a way that conflicts with the old shape. If you need to rename or reorder output columns, drop and recreate instead. The official PostgreSQL CREATE VIEW reference spells out exactly which changes replace allows, and the MySQL views documentation covers the same ground for MySQL. For a broader tour of view related objects, our own views, procedures and triggers guide connects views to stored routines.
3. Why use views
Views earn their place for four reasons. Each one solves a problem that otherwise leaks complexity into every application that reads your database.
Simplify complex joins
A reporting query that joins five tables, filters, and aggregates is painful to write once and miserable to repeat. Wrap it in a view and every consumer selects from one clean name. The join logic lives in exactly one place, so a fix propagates everywhere at once. This is the single most common reason teams adopt views.
Security and column hiding
You can grant a user access to a view without granting access to the base tables. Expose only the safe columns and rows, and sensitive fields never leave the database. For example, a view can drop a salary column or filter to a single tenant. Combined with the practices in our SQL security guide, views become a clean permission boundary:
-- Analysts see names and countries, never the vip flag
CREATE VIEW public_customers AS
SELECT id, name, country
FROM customers;
GRANT SELECT ON public_customers TO analyst_role;Consistent business logic
If revenue is defined as amount after tax and discount, encode that once in a view. Every dashboard then reads the same definition, so two reports can never disagree about what revenue means. This is how views turn tribal knowledge into enforced logic.
Abstraction over schema changes
A view is a stable contract. If you split a table or rename a column, you can update the view definition to keep the old shape, and downstream queries never notice. Applications depend on the view name, not the physical layout, so you gain freedom to refactor.
- One source of truth for joins, filters, and calculations.
- Least privilege access without exposing raw tables.
- Refactor freedom because the view hides the physical schema.
4. Querying a view
You query a view with the exact same syntax you use for a table. You can filter it, join it, aggregate over it, and even build another view on top of it. The database folds your outer query into the view definition before running anything.
-- Total large orders per country, straight off the view
SELECT country, COUNT(*) AS big_orders,
SUM(amount) AS revenue
FROM customer_orders
WHERE size_band = 'large'
GROUP BY country
ORDER BY revenue DESC;| country | big_orders | revenue |
|---|---|---|
| USA | 3 | 1900 |
| Canada | 2 | 1250 |
| UK | 1 | 600 |
Notice that the WHERE clause filters on size_band, a column that does not exist in any base table. It exists only in the view. The optimizer pushes your filter down into the underlying join wherever it safely can, so querying a view is usually no slower than writing the full query yourself. If you want to sharpen the underlying SELECT statements first, our DQL commands guide covers the query building blocks views are made of.
5. Updatable views and their limits
Some views are updatable, meaning you can run INSERT, UPDATE, and DELETE against them and the change flows through to the base table. A view is updatable only when the database can map each output row back to exactly one base table row without ambiguity.
A simple single table view like public_customers is updatable. This works and updates the real customers table:
-- Single table, no aggregation: updatable
UPDATE public_customers
SET country = 'Ireland'
WHERE id = 7;But most interesting views are not updatable, because the database cannot decide which base row a change belongs to. A view stops being updatable as soon as it contains any of the following:
| Construct in the view | Updatable? | Why |
|---|---|---|
| Single table, plain columns | Yes | Rows map one to one |
| JOIN across tables | Usually no | Ambiguous target row |
| Aggregates (SUM, COUNT) | No | One row hides many |
| DISTINCT or GROUP BY | No | Rows are collapsed |
| Computed / CASE columns | Not for those columns | No base column to write |
| UNION | No | Source table unclear |
When you do use an updatable view, add WITH CHECK OPTION to stop writes that would produce rows the view itself could not see. Without it, an insert can vanish from the view immediately after it succeeds, which is confusing and dangerous:
CREATE VIEW uk_customers AS
SELECT id, name, country FROM customers
WHERE country = 'UK'
WITH CHECK OPTION;
-- Rejected: country would not satisfy the view filter
INSERT INTO uk_customers (id, name, country)
VALUES (99, 'Ada', 'France');Watch out: updatable view rules differ sharply by engine. When you truly need to write through a complex, multi table view, use an INSTEAD OF trigger (PostgreSQL and SQL Server) to spell out exactly how each change maps to the base tables. Do not assume a join based view will accept writes.
6. Materialized views
A materialized view is the exception to the rule that views store no data. It runs its query once and physically stores the result, like a cached table. Reads become fast because they hit stored rows instead of re-running an expensive join or aggregation. The tradeoff is that the stored data goes stale until you refresh it.
-- Store the heavy aggregation on disk
CREATE MATERIALIZED VIEW revenue_by_country AS
SELECT country, SUM(amount) AS revenue
FROM customer_orders
GROUP BY country;
-- Rebuild it when the source data changes
REFRESH MATERIALIZED VIEW revenue_by_country;Refresh is the key concept. The stored rows only change when you refresh, either on a schedule, after a batch load, or on demand. PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY so reads are not blocked during the rebuild (it requires a unique index). Terminology varies by engine: Oracle calls them materialized views too, SQL Server offers indexed views that auto maintain, and MySQL has no native materialized view (you emulate one with a real table plus a scheduled refresh job).
Reach for a materialized view when the query is expensive, the underlying data changes far less often than it is read, and a few minutes of staleness is acceptable. Dashboards, nightly rollups, and search indexes are classic fits. If every read must be perfectly current, or the base data churns constantly, a regular view (or a well indexed query) is the better choice.
7. Regular view vs materialized view
The decision between the two comes down to a single question: do you value freshness or read speed more? This table lays the differences out side by side.
| Aspect | Regular view | Materialized view |
|---|---|---|
| Stores data | No, query only | Yes, result on disk |
| Freshness | Always live | Stale until refreshed |
| Read speed | Cost of the query | Fast, precomputed |
| Write / refresh cost | None | Refresh recomputes rows |
| Storage | Near zero | Full result size |
| Can be indexed | Rarely | Yes, like a table |
| Best for | Abstraction, security, live data | Expensive reads on slow changing data |
Rule of thumb: start with a regular view. Promote it to a materialized view only when profiling shows the underlying query is genuinely too slow for its read frequency, and you can tolerate the refresh lag.
8. Performance considerations
A regular view has no performance cost of its own. Its speed is exactly the speed of the query it wraps, because the optimizer inlines the definition before execution. That means every tuning technique for ordinary queries applies unchanged: index the columns the view joins and filters on, avoid selecting columns nobody uses, and check the plan with EXPLAIN. Our SQL performance tuning guide walks through reading those plans in detail.
Two traps deserve special attention. The first is nested views: a view that selects from another view, which selects from a third. Each layer looks tidy, but the optimizer must flatten the whole stack, and deeply nested views frequently confuse it into re-scanning tables, materializing intermediate results, or losing index usage entirely. The second is hidden work: because a view hides its query, developers forget that SELECT * FROM big_view may be triggering a five table join every single time.
Watch out for nested views: stacking views on top of views is convenient but corrodes performance. Each layer the optimizer has to unwrap raises the odds it picks a poor plan or ignores an index. Keep nesting shallow (ideally one level), and if a view is queried constantly, consider flattening it or materializing it instead.
The chart below shows where views typically deliver the most value across common tasks. The bars are representative of how often a view is the right tool, not a benchmark.
9. View use cases at a glance
Bring it together with a short checklist for deciding whether a view fits your problem:
Is the query repeated?
If the same join or filter appears in many places, a view centralizes it so a single edit fixes every consumer.
Do you need to hide columns or rows?
Grant access to a view instead of the base table to expose only safe data. This is a clean least privilege boundary.
Is the read too slow to run live?
If the query is heavy and the data changes slowly, a materialized view trades a little freshness for a large speed gain.
Will you write through it?
Keep the view single table and unaggregated, or plan an INSTEAD OF trigger. Do not assume a join view accepts writes.
Views are a small feature with an outsized payoff: cleaner queries, tighter security, and one authoritative definition of your business logic. Master regular views first, understand exactly when they are updatable, and reach for materialized views only when profiling proves you need the cache. To keep building, the printable SQL cheat sheet puts the view syntax at your fingertips, and the hands on SQL intermediate course has you build and query views against real data.