Views — saving a query under a name

A CTE names a subquery for the length of one query. A view names a query permanently, in the database itself, so it can be queried again later like an ordinary table — without ever actually storing its results.

Intermediate

4 min read

The problem: a complex, useful query that has to be rewritten everywhere

-- This exact join+filter shows up in a dozen different reports and dashboards
SELECT customers.name, orders.id, orders.amount, orders.created_at
FROM customers
JOIN orders ON orders.customer_id = customers.id
WHERE orders.status = 'completed';

If this specific "completed orders with customer names" query is genuinely useful, it likely gets copy-pasted into every report, dashboard, or script that needs it — and if the underlying logic ever needs to change (a new status value, an additional filter), every one of those copies needs updating independently, the exact same "duplicated logic drifts out of sync" problem the earlier lessons in this domain keep returning to.

A view: the query, saved under a name

CREATE VIEW completed_orders AS
SELECT customers.name, orders.id, orders.amount, orders.created_at
FROM customers
JOIN orders ON orders.customer_id = customers.id
WHERE orders.status = 'completed';
 
-- Now query it exactly like a real table:
SELECT * FROM completed_orders WHERE amount > 100;

CREATE VIEW saves a query under a name, permanently, in the database itself — from then on, completed_orders can be queried with a plain SELECT exactly as if it were a real table, including filtering, joining it with other tables, and ordering its results. Every place that needs "completed orders with customer names" now says FROM completed_orders instead of repeating the full join and filter — and if that underlying logic ever needs to change, updating the view's definition once updates it everywhere it's used, instantly.

The crucial fact: a plain view stores no data of its own

-- Every time this runs, the underlying JOIN actually executes fresh —
-- a view is not a snapshot, it's a saved query
SELECT * FROM completed_orders;

A regular view doesn't store any rows — it's purely a saved SELECT statement that gets re-run, in full, every time the view is queried. This means a view is always current (it reflects the underlying tables' data at query time, never stale) but provides zero performance benefit on its own — querying a view runs the exact same underlying JOIN/WHERE work the original query would have, every single time, with no caching involved by default.

Why this matters: a view is about readability and reuse, not speed

-- These two queries do the same amount of actual database work:
SELECT * FROM completed_orders WHERE amount > 100;
 
SELECT customers.name, orders.id, orders.amount, orders.created_at
FROM customers JOIN orders ON orders.customer_id = customers.id
WHERE orders.status = 'completed' AND orders.amount > 100;

A view's real value is the same as a CTE's — hiding a complex query behind a simple, meaningful name — but persisted at the database level instead of scoped to one query. Anyone querying completed_orders doesn't need to know or repeat the underlying join logic; the complexity is centralized in exactly one place, the view's own definition, the same way the Facade pattern centralizes coordination logic behind one simple interface.

A materialized view: the version that actually caches the result

CREATE MATERIALIZED VIEW completed_orders_cached AS
SELECT customers.name, orders.id, orders.amount, orders.created_at
FROM customers JOIN orders ON orders.customer_id = customers.id
WHERE orders.status = 'completed';
 
-- Must be explicitly refreshed to pick up new data:
REFRESH MATERIALIZED VIEW completed_orders_cached;

A materialized view genuinely stores its query's results as actual data on disk, the way a real table does — querying it is fast, since there's no re-running the underlying join every time. The real cost: it goes stale the moment the underlying tables change, and stays stale until explicitly refreshed with REFRESH MATERIALIZED VIEW. This is exactly the caching lesson's trade-off, applied at the database level: a materialized view trades the guarantee of always-current data for real query speed, the same "stale but fast" trade cache-aside makes for application-level caching.

Choosing between a plain view and a materialized view

A plain view is the right default — it's simpler (no refresh logic to manage) and never risks serving stale data. A materialized view is worth the added complexity specifically when the underlying query is genuinely expensive (a heavy aggregation across millions of rows) and gets run often enough that recomputing it every single time is a real, measured cost — and when the application can tolerate the view being slightly behind the live data between refreshes.