A view is a query with a name. It stores no data and owns no rows — it stores the definition, and every time you read it the engine expands it back into the query it replaced. That sounds like a small convenience, and it is, until you notice what it buys: one place where the definition of a business question lives, and every report that asks it gets the same answer.
What are views and when do they earn their keep?
The streaming service in this module records viewing events, and the question the product team asks every week is "what did people actually finish watching?". That question needs three tables and a filter. Here is the schema it asks about — subscribers, the catalogue they browse, and the events they generate:
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
plan TEXT NOT NULL,
city TEXT NOT NULL
);
CREATE TABLE movies (
movie_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
genre TEXT NOT NULL,
release_year INTEGER NOT NULL,
rating REAL NOT NULL,
minutes INTEGER NOT NULL
);
CREATE TABLE viewing_events (
event_id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(user_id),
movie_id INTEGER NOT NULL REFERENCES movies(movie_id),
watched_on TEXT NOT NULL,
device TEXT NOT NULL,
completed INTEGER NOT NULL DEFAULT 0,
minutes_watched INTEGER NOT NULL DEFAULT 0
);
Seven subscribers, ten titles and twenty-four viewing events, each event recording who watched what, when, on which device, and whether they finished:
INSERT INTO users (user_id, name, plan, city) VALUES
(1, 'Amara Diallo', 'standard', 'Lisbon'),
(2, 'Ivan Petrov', 'premium', 'Sofia'),
(3, 'Mei Chen', 'standard', 'Kuala Lumpur'),
(4, 'Nadia Osei', 'standard', 'Accra'),
(5, 'Priya Raman', 'premium', 'Chennai'),
(6, 'Rosa Delgado', 'standard', 'Seville'),
(7, 'Tomas Nowak', 'premium', 'Krakow');
INSERT INTO movies (movie_id, title, genre, release_year, rating, minutes) VALUES
(1, 'The Salt Path Home', 'drama', 2019, 7.8, 118),
(2, 'Night Bus', 'thriller', 2021, 7.1, 104),
(3, 'Kettle Fires', 'drama', 2022, 8.2, 126),
(4, 'Paper Cities', 'documentary', 2018, 7.5, 92),
(5, 'Iron Meadow', 'western', 2024, 6.9, 131),
(6, 'Harbour Lights', 'romance', 2020, 7.3, 110),
(7, 'Salt and Stone', 'drama', 2023, 7.9, 121),
(8, 'The Long Road North', 'thriller', 2019, 6.8, 115),
(9, 'Small Town Radio', 'documentary', 2021, 7.0, 88),
(10, 'Lantern Bay', 'romance', 2024, 7.6, 99);
INSERT INTO viewing_events (event_id, user_id, movie_id, watched_on, device, completed, minutes_watched) VALUES
(1, 1, 1, '2025-03-01', 'tv', 1, 118),
(2, 2, 3, '2025-03-03', 'tv', 1, 126),
(3, 1, 2, '2025-03-04', 'phone', 1, 104),
(4, 3, 5, '2025-03-06', 'laptop', 1, 131),
(5, 2, 1, '2025-03-09', 'laptop', 1, 118),
(6, 1, 3, '2025-03-10', 'tv', 1, 126),
(7, 3, 6, '2025-03-13', 'laptop', 1, 110),
(8, 1, 4, '2025-03-17', 'tablet', 1, 92),
(9, 3, 7, '2025-03-19', 'laptop', 1, 121),
(10, 4, 1, '2025-03-20', 'tablet', 1, 118),
(11, 5, 2, '2025-03-21', 'tv', 1, 104),
(12, 6, 4, '2025-03-22', 'phone', 1, 92),
(13, 7, 7, '2025-03-23', 'tv', 1, 121),
(14, 3, 10, '2025-03-26', 'tv', 1, 99),
(15, 4, 3, '2025-03-27', 'phone', 1, 126),
(16, 5, 5, '2025-03-28', 'laptop', 1, 131),
(17, 6, 9, '2025-03-29', 'tv', 1, 88),
(18, 7, 8, '2025-03-30', 'tablet', 1, 115),
(19, 3, 8, '2025-04-02', 'laptop', 1, 115),
(20, 3, 9, '2025-04-09', 'tv', 1, 88),
(21, 1, 6, '2025-03-08', 'tv', 0, 34),
(22, 2, 2, '2025-03-15', 'phone', 0, 22),
(23, 4, 5, '2025-03-24', 'laptop', 0, 41),
(24, 6, 10, '2025-04-05', 'tv', 0, 17);
Now the question can be written once, as a view. The definition is dropped first so that it can be re-run without complaint:
DROP VIEW IF EXISTS completed_watchlist;
CREATE VIEW completed_watchlist AS
SELECT u.name AS viewer, u.plan, m.title, m.genre, m.release_year, v.watched_on, v.device
FROM viewing_events v
JOIN users u ON u.user_id = v.user_id
JOIN movies m ON m.movie_id = v.movie_id
WHERE v.completed = 1;
And then querying it like any other table:
SELECT * FROM completed_watchlist ORDER BY viewer, watched_on LIMIT 8;
viewer plan title genre release_year watched_on device
------------ -------- ------------------ ----------- ----------- ---------- ------
Amara Diallo standard The Salt Path Home drama 2019 2025-03-01 tv
Amara Diallo standard Night Bus thriller 2021 2025-03-04 phone
Amara Diallo standard Kettle Fires drama 2022 2025-03-10 tv
Amara Diallo standard Paper Cities documentary 2018 2025-03-17 tablet
Ivan Petrov premium Kettle Fires drama 2022 2025-03-03 tv
Ivan Petrov premium The Salt Path Home drama 2019 2025-03-09 laptop
Mei Chen standard Iron Meadow western 2024 2025-03-06 laptop
Mei Chen standard Harbour Lights romance 2020 2025-03-13 laptop
Twenty of the twenty-four viewing events were finished, and the view hides the four that were abandoned. What the engine actually stores is the definition and nothing else:
SELECT type, name FROM sqlite_master WHERE name = 'completed_watchlist';
type name
---- -------------------
view completed_watchlist
A view, not a table. There is no storage for the rows, no separate copy to keep in step, and nothing to migrate when the underlying tables change.
What does a view actually cost?
Expansion. The engine substitutes the view's definition into the query and then plans the result, so a view built from four joins is four joins every time it is read. The plan confirms it — the view is not a separate object the engine can optimise, it is text the engine substitutes:
EXPLAIN QUERY PLAN SELECT * FROM completed_watchlist WHERE viewer = 'Mei Chen';
QUERY PLAN
|--SCAN v
|--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)
Two joins, one scan — exactly what the inner query does. A view therefore buys readability and reuse, not speed. If a view is used constantly and its joins are expensive, the answer is to make the underlying queries index-friendly (lesson 52) rather than to hope the view will be free.
When does a view earn its keep?
- One definition of a business question. When three reports and a dashboard all need "completed watchlist", they all read the same view, and a change to the definition changes all of them at once.
- A stable interface over a changing schema. If the events table is split into two, the view keeps its columns and the reports that use it need no change.
- Permissions. Granting a user SELECT on a view of five columns is safer than granting it on the underlying tables.
- Readability. A name like completed_watchlist carries more meaning than a six-line join repeated in a dozen places.
And when a view is the wrong tool. A view is not a snapshot, so it never shows a consistent picture across a long query — if that matters, use a transaction. A view over a large aggregate is recomputed on every read, so it is the wrong place for an expensive summary. And a view that joins tables one way and then gets joined again usually deserves to be a CTE or a subquery instead, where the optimiser can see the whole picture at once.
How do views differ from CTEs and derived tables?
| View | CTE | Derived table | |
|---|---|---|---|
| Lives for | the life of the database | one statement | one statement |
| Stored | as a schema object | as text in the query | as text in the query |
| Reusable by others | yes, if they have permission | no | no |
| Written once, used many times | yes | yes, within the statement | no |
One more property separates them: some views are updatable. A view over a single table with no aggregation and no DISTINCT can be the target of an UPDATE or a DELETE, and the change lands in the base table — SQLite, PostgreSQL, MySQL and SQL Server all support this for simple views. A view with a join or an aggregate is read-only, because there is no single base row for the change to land in. Whether you want that depends on how much you trust the view's shape to stay simple.
Quick reference
- A view stores a query, not rows; every read re-expands and re-plans it.
- Use a view to give a business question one name and one definition.
- Views give a stable interface over a schema that may change underneath.
- A view with an aggregate or a join is read-only; a simple single-table view may be updatable.
- Views are permanent, CTEs are per-statement, derived tables are per-reference.
View drill: write a view called active_premium_watchers that joins users to viewing_events and keeps only premium subscribers who watched something in the last month — then decide whether it should be updatable, and say why.