Views

A view is a saved query that you can use like a table. It gives a complex join a simple name, hides columns some users shouldn't see, and keeps business rules — like "active customers" or "order totals" — defined in one place instead of copied into every report.

Creating a view

CREATE VIEW order_summary AS
SELECT o.id AS order_id,
       o.order_date,
       o.status,
       c.name AS customer,
       c.country,
       round(SUM(oi.quantity * oi.unit_price), 2) AS total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY o.id, o.order_date, o.status, c.name, c.country;

SELECT * FROM order_summary WHERE country = 'UK';

A view stores the query, not the data. Every time you select from it, the database runs the underlying query, so the results are always current.

Building on a view

CREATE VIEW order_summary AS
SELECT o.id AS order_id, o.order_date, o.status, c.name AS customer, c.country,
       round(SUM(oi.quantity * oi.unit_price), 2) AS total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY o.id, o.order_date, o.status, c.name, c.country;

SELECT country, COUNT(*) AS orders, round(SUM(total), 2) AS revenue
FROM order_summary
WHERE status <> 'cancelled'
GROUP BY country
ORDER BY revenue DESC;

Queries against a view can filter, join, group and sort it — and other views can be built on top of it.

Why use views?

• Simplicity: analysts query order_summary instead of rewriting a four-table join.
• Consistency: "revenue" is calculated one way, everywhere.
• Security: grant access to a view that omits salaries or emails, without granting access to the base table (lesson 50).
• Stability: if base tables are restructured, the view can be updated so existing queries keep working.

A view for soft deletes

ALTER TABLE customers ADD COLUMN deleted_at TEXT;
UPDATE customers SET deleted_at = '2026-03-01' WHERE id = 8;

CREATE VIEW active_customers AS
SELECT id, name, email, city, country, signup_date
FROM customers
WHERE deleted_at IS NULL;

SELECT COUNT(*) AS active FROM active_customers;

Changing and removing views

CREATE VIEW cheap_products AS SELECT name, price FROM products WHERE price < 20;
DROP VIEW cheap_products;
CREATE VIEW cheap_products AS SELECT name, price, category FROM products WHERE price < 15;

SELECT * FROM cheap_products;

PostgreSQL, MySQL and Oracle support CREATE OR REPLACE VIEW, and SQL Server has CREATE OR ALTER VIEW. In SQLite, drop and recreate the view.

Updating through views

Simple views over a single table (no grouping, no DISTINCT) are often updatable in PostgreSQL, MySQL and SQL Server: an UPDATE on the view changes the base table. WITH CHECK OPTION stops changes that would make a row disappear from the view. SQLite views are read-only unless you add INSTEAD OF triggers.

Materialized views

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', order_date) AS month, SUM(total) AS revenue
FROM order_summary
GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_revenue;

A materialized view stores its results physically, like a table. Reading it is fast, but the data is only as fresh as the last refresh. PostgreSQL and Oracle support them (SQL Server has "indexed views"); in other databases, a summary table refreshed on a schedule plays the same role.

Try it yourself

Create a view of each employee with their department and manager names, then query it.