Skip to content
Tutorials Center
My learning

Topics

Lesson 76 of 125 ยท Designing Databases

Views

Save queries as virtual tables with SQL views. Learn CREATE VIEW, how views stay up to date, updatable views, INSTEAD OF triggers and materialized views.

Some queries you write once and then need again and again: "order totals with shipping", "products running low on stock", "active customers with their city". Copying a ten-line join into every report is tedious and risky, because one day someone changes it in one place and not the others. A view solves that. It's a query saved in the database under a name, which you can then use as if it were a table.

A view stores no data of its own. Every time you select from it, the database runs the saved query against the current tables, so the view is always up to date. That makes views a great way to give complicated queries a simple name, to hide columns people shouldn't see, and to keep old queries working while the tables underneath change.

This lesson shows how to create and use views, what happens when you try to change data through one, how to make that work in SQLite with INSTEAD OF triggers, and how materialized views in other databases trade freshness for speed.

Creating a view

CREATE VIEW name AS followed by any SELECT creates a view. Then you query it like a table:

SQL A view over a join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Four products are low on stock, and three of them are sold out. Anyone who needs this list can now write SELECT * FROM low_stock_products without knowing how the join or the threshold works.

A view's column names come from the query's column names and aliases, so give every expression a clear alias. You can also list the names right after the view name: CREATE VIEW low_stock_products (id, product, category, price, stock) AS SELECT โ€ฆ.

Views are always up to date

Because a view is only a saved query, changes to the underlying tables show up immediately:

SQL Views read live data ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first count is 4. After the two updates, the spatula set (product 5) has left the list and the skillet (product 1) has joined it. Nothing had to "refresh" the view.

That also means a view is exactly as fast as the query behind it. A view over a slow query is a slow view; the indexes on the underlying tables are what make it fast (see Indexes). In SQLite, PostgreSQL and most other databases, the planner merges your outer query into the view's query when it can, so WHERE conditions on the view can still use those indexes.

Building reports on views

Views can contain joins, aggregates, CASE expressions, subqueries, window functions and CTEs. A common pattern is to put the hard part of a calculation into one view and then write many simple reports on top of it:

SQL Reports on top of a view ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The largest order is order 88 at 1090.8, and delivered orders bring in 13534.45 across 57 orders. Neither report repeats the join or the SUM: they trust the view to define "order total" once. If the definition changes (say, discounts are added), you change one view, and every report follows.

Views can also build on other views. Keep the chain short, though: three layers of views over views quickly become hard to understand and to optimize.

Views for security and simplicity

Views are also a way to show only part of a table. Suppose a support dashboard should list customers but not their email addresses:

SQL A view that hides columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In server databases, you combine this with permissions: grant a reporting role SELECT on customer_directory but not on customers, and that role can never read an email address (see Users, Roles and Permissions). SQLite has no user accounts, so in SQLite a view is a convenience, not a security boundary.

Views also help with schema changes. If you rename or split a table, you can create a view with the old name and shape, so existing reports and code keep working while they're migrated (see ALTER TABLE and DROP TABLE).

Changing and dropping views

You can't edit a view in place in SQLite. Drop it and create it again; data isn't affected, because a view has none:

SQL
DROP VIEW IF EXISTS low_stock_products;

CREATE VIEW low_stock_products AS
SELECT id, name, stock
FROM products
WHERE stock < 20;          -- new threshold

Other dialects offer shortcuts: PostgreSQL has CREATE OR REPLACE VIEW (as long as existing columns keep their names and types), MySQL has CREATE OR REPLACE VIEW and ALTER VIEW, and SQL Server has CREATE OR ALTER VIEW and ALTER VIEW. All of them, like SQLite, support CREATE VIEW IF NOT EXISTS or DROP VIEW IF EXISTS in some form.

SQLite also supports CREATE TEMP VIEW, which exists only for the current connection, handy for one-off analysis sessions.

SQLite doesn't check views against later table changes. If you drop a column or table that a view uses, the view stays in the schema but fails with an error the next time you query it. PostgreSQL protects you instead: it refuses to drop objects that views depend on unless you add CASCADE, which drops the views too.

Can you INSERT into a view?

In SQLite, views are read-only. INSERT, UPDATE and DELETE on a view fail. This example fails on purpose:

SQL SQLite views are read-only ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The count is 24, then the UPDATE stops with Error: cannot modify pending_orders because it is a view.

Other databases are more generous. PostgreSQL, MySQL and SQL Server make a view automatically updatable when it's simple enough that each view row maps to exactly one table row: one table in FROM (MySQL and SQL Server also allow some joins), no GROUP BY, DISTINCT, aggregates, UNION or window functions. Changing pending_orders above would work there, and would update orders.

That raises a subtle problem: what if you update a row so it no longer matches the view? In PostgreSQL, setting status = 'shipped' through pending_orders succeeds, and the row then disappears from the view. WITH CHECK OPTION forbids such changes:

SQL
-- PostgreSQL, MySQL, SQL Server
CREATE VIEW pending_orders AS
SELECT id, customer_id, ordered_at, status
FROM orders
WHERE status = 'pending'
WITH CHECK OPTION;

UPDATE pending_orders SET status = 'shipped' WHERE id = 3;
-- ERROR: new row violates check option for view "pending_orders"

INSTEAD OF triggers

SQLite (and PostgreSQL, SQL Server and Oracle) let you make any view writable with an INSTEAD OF trigger: a piece of SQL that runs instead of the INSERT, UPDATE or DELETE on the view and decides what to do with the real tables. Inside the trigger, NEW is the row being written and OLD is the row being replaced.

SQL An INSTEAD OF trigger makes a view writable ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The new mat appears with the category "Home Office", even though products stores only a category_id: the trigger translated the name. The headlamp's price changed in products. The view now behaves like a friendlier table. Triggers have many more uses, covered in Triggers.

Materialized views

A normal view recomputes its query every time. For an expensive report over millions of rows, that can be too slow. A materialized view stores the query's result as real data and serves it instantly, at the cost of being stale until it's refreshed:

SQL
-- PostgreSQL
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  date_trunc('month', o.ordered_at) AS month,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 1;

CREATE UNIQUE INDEX ON monthly_revenue (month);   -- materialized views can be indexed

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;   -- recompute without blocking readers

Oracle has materialized views that can refresh automatically, and SQL Server has indexed views, which the server keeps up to date on every write. MySQL and SQLite have none. In SQLite you emulate one with a summary table that you rebuild when needed, for example every night:

SQL A materialized view in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The summary has one row per month, ready to read without touching order_items. The trade-off is the same as in PostgreSQL: fast reads, but the numbers are only as fresh as the last refresh. A trigger-maintained summary table can keep it current on every write instead.

View Materialized view
Stores data No, only the query Yes, the result
Freshness Always current As of the last refresh
Read speed Same as the query Like reading a table
Can be indexed No (uses the base tables' indexes) Yes
Support Every database PostgreSQL, Oracle; SQL Server indexed views; emulated in SQLite and MySQL

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A view v is defined as SELECT * FROM products WHERE stock = 0. A product's stock is updated from 5 to 0. When does it appear in v?

    A normal view stores only its query, which runs against current data every time you use the view.
  2. 2. What happens when you run UPDATE on a view in SQLite without any triggers?

    SQLite views are read-only. An INSTEAD OF trigger is needed to make writes through a view work.
  3. 3. Which view is automatically updatable in PostgreSQL?

    A view over a single table without aggregates, DISTINCT or UNION maps each view row to exactly one table row, so it is automatically updatable.
  4. 4. What is the main trade-off of a materialized view?

    A materialized view stores the result, so reads are fast but the data must be refreshed to reflect changes. It uses more storage, not less.
  5. 5. Why is a view not a security boundary in SQLite?

    Views can hide columns anywhere, but only databases with permissions (GRANT/REVOKE) can stop users from reading the base table directly.

Summary

  • A view is a named, saved SELECT you query like a table; it stores no data.
  • Views always show current data and are as fast as the query behind them.
  • Use views to name complex logic once, build simple reports on top, hide columns and keep old table names working.
  • Change a view in SQLite with DROP VIEW + CREATE VIEW; other databases have CREATE OR REPLACE / ALTER VIEW.
  • SQLite views are read-only; simple single-table views are automatically updatable in PostgreSQL, MySQL and SQL Server, and WITH CHECK OPTION keeps writes inside the view.
  • INSTEAD OF triggers make any view writable by translating writes into changes to real tables.
  • Materialized views store results for fast reads and must be refreshed; emulate them in SQLite with a summary table.

Next: Entity-Relationship Modeling, where you'll design a whole database before writing any SQL.