Views: saved queries you can use like tables

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  T["orders + customers<br/>(real tables)"] -->|"view runs its query<br/>every time"| V["customer_totals<br/>(view: no data stored)"]
  V --> Q["your SELECT"]
  T -.->|"copied at refresh"| M["materialised view<br/>(stored rows)"]

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.

The shape of it

CREATE VIEW view_name AS
SELECT ...;

SELECT * FROM view_name WHERE ...;

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

CREATE VIEW customer_totals AS
SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS orders,
       COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

SELECT name, total_spent
FROM customer_totals
WHERE total_spent > 50
ORDER BY total_spent DESC;

CREATE VIEW customer_totals AS
SELECT c.customer_id, c.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

INSERT INTO orders VALUES (111, 4, '2024-03-25', 90, 'pending');

SELECT name, total_spent FROM customer_totals WHERE customer_id = 4;

Must-know points

• A view is a saved query, used like a table. It stores no rows.
• Views give one shared definition of a metric, and can hide sensitive columns.
• A view is as fast as its underlying query — it doesn't speed things up.
• A materialised view stores the result: fast reads, stale until refreshed.

Common mistakes

• Expecting a normal view to make a slow query fast.
• Stacking views on views until no one can tell what a query really does.
• Forgetting to refresh a materialised view and reporting old numbers.

In other databases

• Materialised views: PostgreSQL CREATE MATERIALIZED VIEW + REFRESH MATERIALIZED VIEW; Oracle has them; SQL Server uses "indexed views"; MySQL and SQLite have none.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL Views

  short page with a try-it editor, free

• Read

  PostgreSQL docs — Materialized Views

  official documentation, free

• Read

  SQLite docs — CREATE VIEW

  official documentation, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain the difference between a view and a materialised view.

   Show answer

   A view is a saved query with a name. It stores no rows: every time you select from it, it runs its query against the current tables, so it is always up to date, and only as fast as that query.

   A materialised view runs the query once and stores the result rows. Reading it is fast (it is like reading a table), but it is a snapshot: new orders don't appear until you REFRESH it. Use a view for one shared definition of a query; use a materialised view when the query is expensive and slightly old numbers are fine (such as a dashboard refreshed every hour).

2. Writefrom a blank page, then run it

   Create a view product_sales with each product and the total quantity sold (0 if none), then select the top 3 from it.

   Show answer

   The LEFT JOIN keeps every product, and COALESCE turns the phone case's NULL total into 0. The view is then queried like a table.

   The desk lamp and the mug tie on 6 for second place; the name in the ORDER BY decides which comes first, and both make the top 3 here.

   sqlCopy

   CREATE VIEW product_sales AS
   SELECT p.product_id, p.product_name, COALESCE(SUM(oi.quantity), 0) AS total_sold
   FROM products p
   LEFT JOIN order_items oi ON oi.product_id = p.product_id
   GROUP BY p.product_id, p.product_name;
   
   SELECT product_name, total_sold
   FROM product_sales
   ORDER BY total_sold DESC, product_name
   LIMIT 3;

   Try it

   3 rows

   product_name	total_sold
   Headphones	11
   Desk lamp	6
   Mug	6

3. Predictsay it before you run it

   If you UPDATE an order amount, does SELECT from customer_totals show the new total? Would a materialised view?

   Show answer

   Yes. The view stores only its query, so the next SELECT re-runs it against the updated orders table. Below, order 109 goes from £30 to £50, and Priya's total goes from 425 to 445 straight away.

   A materialised view would not: it would keep showing 425 until someone ran REFRESH MATERIALIZED VIEW.

   sqlCopy

   CREATE VIEW customer_totals AS
   SELECT c.customer_id, c.name, COALESCE(SUM(o.amount), 0) AS total_spent
   FROM customers c
   LEFT JOIN orders o ON o.customer_id = c.customer_id
   GROUP BY c.customer_id, c.name;
   
   UPDATE orders SET amount = 50 WHERE order_id = 109;
   
   SELECT name, total_spent FROM customer_totals WHERE customer_id = 1;

   Try it

   1 row

   name	total_spent
   Priya	445

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Your view uses SELECT * from a table, and someone adds a column to that table. What might happen? Why is it safer to list columns?

   Show answer

   It depends on the database, and every option is bad.

   • SQLite expands the * every time the view is read, so the new column appears in the view by itself. If that column is sensitive, it leaks. Below, a view meant to be safe to share suddenly shows a password_hash.
   • PostgreSQL expands the * once, when the view is created, so the new column never appears, and the view quietly stops matching its table. Dropping a column the view uses, or changing its type, is refused.
   • In SQL Server, the view's stored column list can go out of step with the table until it is refreshed, and columns can come back shifted.

   Listing the columns makes the view's shape fixed and visible: nothing new appears by accident, nothing sensitive leaks, and anything that depends on the view keeps getting the same columns.

   sqlCopy

   CREATE VIEW public_customers AS
   SELECT * FROM customers;
   
   ALTER TABLE customers ADD COLUMN password_hash TEXT;
   UPDATE customers SET password_hash = 'x9f2…' WHERE customer_id = 1;
   
   SELECT * FROM public_customers WHERE customer_id = 1;

   Try it

   1 row

   customer_id	name	email	city	signup_date	password_hash
   1	Priya	priya@mail.com	London	2024-01-02	x9f2…

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

A view is a stored query that behaves like a virtual table: it holds no data, so it is always current, and 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.

Follow-up questions

Tick each check when you can do it without notes