A view is a query with a name. It stores no data and it owns no rows. What it keeps is the definition, and every time you read it the engine expands it back into the query it replaced. At first that looks like a small convenience, and it is, until you notice what it buys. There is one place where the definition of a business question lives, and every report that asks the question gets the same answer.
This is the first lesson in the Views, Indexes and Performance module, and the next lesson in this module is about "How Do Indexes Speed Reads and Slow Writes?".
What are the views, and when do they earn their keep?
The streaming service in this module records the viewing events, and the product team asks one question every week: "what did the people actually finish watching?"
That question needs three tables and a filter. Here is the schema it asks about. There are the subscribers, there is the catalogue they browse, and there are 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
);There are seven subscribers, ten titles, and twenty-four viewing events. Each event records 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 a 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 you query 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 laptopTwenty 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_watchlistIt is a view and not a table. There is no storage for the rows, there is no separate copy to keep in step, and there is nothing to migrate when the underlying tables change. The rows still live in users, movies, and viewing_events, and they are still updated there. The view only knows how to ask for them.
What does the view actually cost?
It costs the expansion. The engine substitutes the definition of the view into the query and then it plans the result, so a view that is built from four joins is four joins every time that it is read. Nothing is carried over from one read to the next.
The plan confirms it. The view is not a separate object that the engine can optimise. It is the text that the engine substitutes, so the optimiser sees the same query every time and makes the same choice every time.
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=?)There are two joins and one scan, which is exactly what the inner query does.
A view therefore buys the readability and the reuse, and not the speed. If a view is used constantly and its joins are expensive, then the answer is to make the underlying queries index-friendly in lesson 52, rather than to hope that the view will be free. A view never makes a slow query fast, but it can certainly hide how slow the query has become.
When does a view earn its keep?
- One definition of a business question. When three reports and a dashboard all need the "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 the
SELECTon a view of five columns is safer than granting it on the underlying tables. - Readability. A name like
completed_watchlistcarries more meaning than a six-line join that is repeated in a dozen places.
And there are times 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, then you 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 the 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. In that case the extra layer only makes the plan harder to read.
How do the views differ from the CTEs and the 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 |
There is one more property that 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 of a DELETE, and the change lands in the base table. SQLite, PostgreSQL, MySQL and the SQL Server all support this for the simple views.
A view with a join or with 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 shape of the view to stay simple.
Quick reference
- A view stores a query and not rows, and every read re-expands and re-plans it.
- You 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 with a join is read-only, and a simple single-table view may be updatable.
- Views are permanent, CTEs are per-statement, and derived tables are per-reference.
Try it yourself: write a view called active_premium_watchers that joins the users to the viewing_events and that keeps only the premium subscribers who watched something in the last month. Then decide whether it should be updatable, and say why.