Views

Opening your lesson

A view is a saved query with a name. You query it like a table, and every time you do, the database runs the saved query against the current data:

CREATE VIEW active_members AS
SELECT membership_id, customer_id, plan
FROM memberships
WHERE ended_on IS NULL;

Views give a team one agreed definition ("active member", "revenue") instead of five slightly different copies in five reports. They also hide complexity: a view over a three-table join is as easy to query as one table.

CREATE OR REPLACE VIEW changes a view's query; DROP VIEW removes it. A view stores no data, so it's always up to date, and it's only as fast as its query. PostgreSQL, Snowflake and others also have materialized views, which store the result and must be refreshed.

CREATE VIEW customer_spend AS
SELECT c.customer_id, c.first_name, COUNT(*) AS orders, SUM(o.total) AS spent
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name;

A join and a summary, saved under one name.

Choose your character

Your Work Gym account