Views: saved queries you can use like tables

A view is a named, saved SELECT that you query like a table. It stores no data of its own, so it always shows the current data. A materialised view stores the result and must be refreshed.

Why it matters

The same "customer totals" query gets copied into ten reports, and when the rule changes, nine get fixed. A view keeps one definition in the database that everyone uses. Views can also hide columns people shouldn't see.

The idea

CREATE VIEW customer_totals AS SELECT … saves the query under a name. SELECT * FROM customer_totals runs it. Because the view is only a query, a new order shows up the next time you read it. A materialised view (PostgreSQL, Oracle) is different: it saves the rows, so reading is fast, but the data is only as fresh as the last REFRESH.

A saved search in your email: "unread from my manager". It doesn't copy any emails; it runs the search every time you open it. A materialised view is a printed list of those emails — fast to read, but out of date as soon as a new one arrives.

A saved search is free to run. A view over a big, complex query costs the full query every time you read it — a view makes queries shorter to write, not faster to run.

Syntax

CREATE VIEW view_name AS
SELECT ...;

SELECT * FROM view_name WHERE ...;

How it works

  1. CREATE VIEW stores the query text under the name customer_totals.
  2. Selecting from the view runs that query and then applies your WHERE and ORDER BY.
  3. Insert a new order for Omar, read the view again, and his total is already there.
  4. A materialised view would still show his old total until it is refreshed.

Key points

Common mistakes

Not true: Saving a slow query as a view makes it run faster.
In fact: A normal view stores no rows: its query runs again every time the view is used. Only a materialised view stores results, and it has to be refreshed.

In the interview

A view is a stored query that behaves like a virtual table. It holds no data, so it is always current. It is useful for sharing one definition of a metric, or for restricting which columns people see. It doesn't make queries faster. When the query is expensive and slight staleness is fine, I use a materialised view, which stores the result and is refreshed on a schedule.

Part of Unit 6: Building tables and changing data in the free SQL course.