{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"views-and-saved-questions","title":"Views: a saved question, not a saved answer","url":"https://addaly.com/learn/data-and-sql/views-and-saved-questions","position":62,"of":66,"minutes":8,"answer":"A view stores a question and re-runs it on every reference, so it is always current and never faster than its query; a materialised view stores the answer, is as stale as its last refresh.","keyIdea":"A view stores a question and re-runs it on every reference, so it is always current and never faster than its query; a materialised view stores the answer, is as stale as its last refresh, and must say so."},"body":{"format":"markdown","text":"## One definition, used everywhere\n\nThree reports each define \"active customer\" in their own WHERE clause, and the three definitions have drifted. The fix is not to align the three clauses; it is to have one, in one place, that the three reports read from:\n\n```sql\nCREATE VIEW active_customers AS\nSELECT c.*\nFROM customers c\nWHERE c.deleted_at IS NULL\n  AND EXISTS (SELECT 1 FROM orders o\n              WHERE o.customer_id = c.id\n                AND o.placed_at >= current_date - interval '90 days');\n```\n\n`SELECT count(*) FROM active_customers` now means the same thing in every report, and when the business changes the definition to 60 days, it changes in one line and every report follows.\n\n## What a view is, mechanically\n\nA view stores the query, not its result. Every time it is referenced, the engine substitutes the view's definition into the calling query and plans the whole thing together, which means a view is exactly as fast or slow as the query it wraps, and it always reflects the current data. Filters applied to the view are pushed inside it where the planner can: `SELECT * FROM active_customers WHERE city = 'Pune'` uses an index on city if one exists, because after substitution it is a plain query over `customers`.\n\nThat last point has a limit. A view containing `DISTINCT`, an aggregate, or a window function is a barrier that outer filters cannot always be pushed through, and a view over a view over a view can produce a plan nobody can read. Views are for naming, not for stacking.\n\n## Views as an interface\n\nA view can hide columns: `CREATE VIEW customers_public AS SELECT id, display_name, city FROM customers` leaves out phone, email and date of birth, and an analyst role granted access to the view and not the table cannot reach them. It can present a stable shape over tables that change: when `orders` is split into two tables next year, a view named `orders` with the old columns keeps every existing report working. And it can rename the awkward: a view over a vendor's `tbl_cust_mstr` called `customers` is a small kindness to everyone who comes after.\n\n## Materialised views: when the answer is worth storing\n\nA view whose query takes forty seconds is a report that takes forty seconds every time somebody opens it. A materialised view runs the query once and stores the rows:\n\n```sql\nCREATE MATERIALIZED VIEW monthly_revenue AS\nSELECT date_trunc('month', placed_at) AS month, city, sum(amount) AS revenue\nFROM orders GROUP BY 1, 2;\n\nREFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;\n```\n\nReads are now instant, and the data is as old as the last refresh. That staleness is a property, not a defect, and it belongs in the report's title: \"as of 06:00 today\". `CONCURRENTLY` lets reads continue during the refresh and requires a unique index on the materialised view; without it the refresh locks readers out for its duration. Schedule the refresh, and put its timestamp somewhere a query can read, because \"the dashboard is wrong\" and \"the dashboard is six hours old\" are different conversations.\n\nDuckDB and SQLite have views and not materialised ones; the equivalent is a table rebuilt by a scheduled `CREATE TABLE ... AS`. BigQuery has materialised views that refresh themselves within limits.\n\n## Writing through a view\n\nA simple view over one table, with no aggregate or join, accepts `INSERT`, `UPDATE` and `DELETE` and passes them to the table. `WITH CHECK OPTION` refuses a write through the view that would produce a row the view cannot see, so that an insert through `active_customers` cannot create a deleted customer. Anything more complicated needs a trigger, and at that point a view is the wrong tool.\n\n## Dependencies\n\n`ALTER TABLE customers DROP COLUMN city` fails if a view uses `city`, with an error naming the view. That is a feature: the database knows what depends on what and will not let a schema change silently break a report. `DROP ... CASCADE` overrides it and drops the views too, which is the option to read twice before typing.\n\n## The habit\n\nWhen a definition matters to more than one query, make it a view. When a view is slow and its data can be minutes old, make it materialised and print the refresh time. When a view has a view inside it, ask whether one of them should be a table."},"check":{"question":"A dashboard reads from a materialised view refreshed nightly at 02:00. At 15:00 a manager sees a revenue figure that does not include a large order placed at 10:00. Which statement is true?","options":[{"text":"The view's query has a bug that excludes large orders, since a view always reflects the current table contents.","correct":false,"tempting":"A plain view does; a materialised view stores rows from its last refresh, and 10:00 is after 02:00."},{"text":"The order was inserted inside an uncommitted transaction and is invisible to every reader, including the dashboard.","correct":false,"tempting":"Possible in principle, but the stated refresh schedule explains the gap without any assumption about the order's transaction."},{"text":"The figure is correct as of 02:00, and the dashboard should show its refresh time so the reader knows what \"as of\" applies.","correct":true},{"text":"`REFRESH MATERIALIZED VIEW CONCURRENTLY` only picks up rows inserted since the previous refresh, and the order will appear after two more refreshes.","correct":false,"tempting":"A refresh re-runs the whole query and replaces the stored rows; the order will appear at the next refresh."}]},"prev":{"slug":"altering-a-live-table","title":"Changing a table that is in use","url":"https://addaly.com/learn/data-and-sql/altering-a-live-table"},"next":{"slug":"json-columns","title":"JSON in a column: when it helps and what it costs","url":"https://addaly.com/learn/data-and-sql/json-columns"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Views: a saved question, not a saved answer', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/views-and-saved-questions (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Views: a saved question, not a saved answer","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"A view stores a question and re-runs it on every reference, so it is always current and never faster than its query; a materialised view stores the answer, is as stale as its last refresh, and must say so.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"one-definition-used-everywhere","runs":[{"text":"One definition, used everywhere"}]},{"t":"p","runs":[{"text":"Three reports each define “active customer” in their own WHERE clause, and the three definitions have drifted. The fix is not to align the three clauses; it is to have one, in one place, that the three reports read from:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE VIEW active_customers AS\nSELECT c.*\nFROM customers c\nWHERE c.deleted_at IS NULL\n  AND EXISTS (SELECT 1 FROM orders o\n              WHERE o.customer_id = c.id\n                AND o.placed_at >= current_date - interval '90 days');"},{"t":"p","runs":[{"text":"SELECT count(*) FROM active_customers","code":true},{"text":" now means the same thing in every report, and when the business changes the definition to 60 days, it changes in one line and every report follows."}]},{"t":"h2","id":"what-a-view-is-mechanically","runs":[{"text":"What a view is, mechanically"}]},{"t":"p","runs":[{"text":"A view stores the query, not its result. Every time it is referenced, the engine substitutes the view’s definition into the calling query and plans the whole thing together, which means a view is exactly as fast or slow as the query it wraps, and it always reflects the current data. Filters applied to the view are pushed inside it where the planner can: "},{"text":"SELECT * FROM active_customers WHERE city = 'Pune'","code":true},{"text":" uses an index on city if one exists, because after substitution it is a plain query over "},{"text":"customers","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"That last point has a limit. A view containing "},{"text":"DISTINCT","code":true},{"text":", an aggregate, or a window function is a barrier that outer filters cannot always be pushed through, and a view over a view over a view can produce a plan nobody can read. Views are for naming, not for stacking."}]},{"t":"h2","id":"views-as-an-interface","runs":[{"text":"Views as an interface"}]},{"t":"p","runs":[{"text":"A view can hide columns: "},{"text":"CREATE VIEW customers_public AS SELECT id, display_name, city FROM customers","code":true},{"text":" leaves out phone, email and date of birth, and an analyst role granted access to the view and not the table cannot reach them. It can present a stable shape over tables that change: when "},{"text":"orders","code":true},{"text":" is split into two tables next year, a view named "},{"text":"orders","code":true},{"text":" with the old columns keeps every existing report working. And it can rename the awkward: a view over a vendor’s "},{"text":"tbl_cust_mstr","code":true},{"text":" called "},{"text":"customers","code":true},{"text":" is a small kindness to everyone who comes after."}]},{"t":"h2","id":"materialised-views-when-the-answer-is-worth-storing","runs":[{"text":"Materialised views: when the answer is worth storing"}]},{"t":"p","runs":[{"text":"A view whose query takes forty seconds is a report that takes forty seconds every time somebody opens it. A materialised view runs the query once and stores the rows:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE MATERIALIZED VIEW monthly_revenue AS\nSELECT date_trunc('month', placed_at) AS month, city, sum(amount) AS revenue\nFROM orders GROUP BY 1, 2;\n\nREFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;"},{"t":"p","runs":[{"text":"Reads are now instant, and the data is as old as the last refresh. That staleness is a property, not a defect, and it belongs in the report’s title: “as of 06:00 today”. "},{"text":"CONCURRENTLY","code":true},{"text":" lets reads continue during the refresh and requires a unique index on the materialised view; without it the refresh locks readers out for its duration. Schedule the refresh, and put its timestamp somewhere a query can read, because “the dashboard is wrong” and “the dashboard is six hours old” are different conversations."}]},{"t":"p","runs":[{"text":"DuckDB and SQLite have views and not materialised ones; the equivalent is a table rebuilt by a scheduled "},{"text":"CREATE TABLE ... AS","code":true},{"text":". BigQuery has materialised views that refresh themselves within limits."}]},{"t":"h2","id":"writing-through-a-view","runs":[{"text":"Writing through a view"}]},{"t":"p","runs":[{"text":"A simple view over one table, with no aggregate or join, accepts "},{"text":"INSERT","code":true},{"text":", "},{"text":"UPDATE","code":true},{"text":" and "},{"text":"DELETE","code":true},{"text":" and passes them to the table. "},{"text":"WITH CHECK OPTION","code":true},{"text":" refuses a write through the view that would produce a row the view cannot see, so that an insert through "},{"text":"active_customers","code":true},{"text":" cannot create a deleted customer. Anything more complicated needs a trigger, and at that point a view is the wrong tool."}]},{"t":"h2","id":"dependencies","runs":[{"text":"Dependencies"}]},{"t":"p","runs":[{"text":"ALTER TABLE customers DROP COLUMN city","code":true},{"text":" fails if a view uses "},{"text":"city","code":true},{"text":", with an error naming the view. That is a feature: the database knows what depends on what and will not let a schema change silently break a report. "},{"text":"DROP ... CASCADE","code":true},{"text":" overrides it and drops the views too, which is the option to read twice before typing."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"When a definition matters to more than one query, make it a view. When a view is slow and its data can be minutes old, make it materialised and print the refresh time. When a view has a view inside it, ask whether one of them should be a table."}]}],"check":{"question":[{"text":"A dashboard reads from a materialised view refreshed nightly at 02:00. At 15:00 a manager sees a revenue figure that does not include a large order placed at 10:00. Which statement is true?"}],"options":[{"text":[{"text":"The view’s query has a bug that excludes large orders, since a view always reflects the current table contents."}],"correct":false,"tempting":[{"text":"A plain view does; a materialised view stores rows from its last refresh, and 10:00 is after 02:00."}]},{"text":[{"text":"The order was inserted inside an uncommitted transaction and is invisible to every reader, including the dashboard."}],"correct":false,"tempting":[{"text":"Possible in principle, but the stated refresh schedule explains the gap without any assumption about the order’s transaction."}]},{"text":[{"text":"The figure is correct as of 02:00, and the dashboard should show its refresh time so the reader knows what “as of” applies."}],"correct":true,"tempting":null},{"text":[{"text":"REFRESH MATERIALIZED VIEW CONCURRENTLY","code":true},{"text":" only picks up rows inserted since the previous refresh, and the order will appear after two more refreshes."}],"correct":false,"tempting":[{"text":"A refresh re-runs the whole query and replaces the stored rows; the order will appear at the next refresh."}]}],"order":[1,2,0,3],"why":null},"prev":{"slug":"altering-a-live-table","title":"Changing a table that is in use","minutes":9},"next":{"slug":"json-columns","title":"JSON in a column: when it helps and what it costs","minutes":9}}}