Learn

A view is a saved query — a virtual table whose contents are computed on demand. Use them to encapsulate complex queries, restrict access, or provide a stable API over an evolving schema.

Basic view

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE deleted_at IS NULL;

-- Use it like a table:
SELECT * FROM active_users WHERE email LIKE '%@example.com';

Under the hood, the DB inlines the view's definition — same as if you'd written the full query.

Drop and replace:

DROP VIEW IF EXISTS active_users;
CREATE OR REPLACE VIEW active_users AS ...   -- Postgres

Why use views

1. Encapsulation. A complex 3-table join becomes one name:

CREATE VIEW order_summary AS
SELECT u.name, o.id AS order_id, p.title, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN users u ON u.id = o.user_id;

Queries using order_summary don't know (or care) about the underlying schema. Refactor without breaking consumers.

2. Security. Grant SELECT on a view that hides sensitive columns:

CREATE VIEW public_users AS SELECT id, name, country FROM users;  -- no email/SSN
GRANT SELECT ON public_users TO reporting_role;

3. Stable API. When the schema changes, update the view. Consumers don't need to update.

Updatable views

In Postgres and MySQL, simple views are automatically updatable:

UPDATE active_users SET name = 'Ada L.' WHERE id = 1;
-- Translates to UPDATE on the underlying table.

Views with joins, GROUP BY, or DISTINCT are NOT updatable. Use INSTEAD OF triggers (Postgres) for fancier behavior.

Materialized views

A materialized view stores the result. Faster reads, stale until refreshed:

-- Postgres syntax:
CREATE MATERIALIZED VIEW daily_stats AS
SELECT date, COUNT(*) AS events, SUM(amount) AS total
FROM events
GROUP BY date;

REFRESH MATERIALIZED VIEW daily_stats;     -- recompute
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats;  -- without blocking reads

Use for expensive aggregates that don't need to be live (yesterday's sales, monthly summaries).

SQLite doesn't have materialized views — work around with regular tables refreshed by triggers or app code.

Common patterns

Hide soft-deletes:

CREATE VIEW users AS SELECT * FROM users_all WHERE deleted_at IS NULL;

Double-naming is a smell — usually you put a users view over a users_table or use a column predicate.

Compatibility shim during a migration:

-- Old code references 'username'. New schema uses 'handle'.
CREATE VIEW old_users AS SELECT id, handle AS username, email FROM users;

Aggregation API:

CREATE VIEW daily_active_users AS
SELECT date, COUNT(DISTINCT user_id) AS dau
FROM logins
GROUP BY date;

When NOT to use

• Performance-critical queries — views can hide costs. Use the raw query and EXPLAIN it.
• Heavy joins — the optimizer doesn't always inline well. Test with EXPLAIN.
• Trivial wrappers — "SELECT * FROM users WHERE id = ?" is not a view.

Views vs CTEs vs subqueries

• View — persisted in schema, reusable, lives in DB metadata
• CTE — defined per query, scoped to one statement
• Subquery — inline, anonymous

Views for cross-query reuse. CTEs for within-query clarity. Subqueries for one-shot use.