Stop 06 · SQL DatabasesTopics · 11/14

Views

In short

Writing the same five-line join in ten places? A view saves that query under a name, so you can SELECT from it like a table.

Saved, reusable SQL queries that behave like virtual tables — useful for simplifying complex joins or restricting what columns/rows certain users can see.

The blob stands at a canteen window showing a few dishes, with a storeroom half hidden behind a curtain.

Think of it like this

The canteen serving window

Behind the canteen counter is a storeroom full of sacks, crates and half-cut veggies. Students only see the serving window with today's ready dishes. A view is that window: a neat, chosen picture of messy tables, without moving anything.

Watch it happen

What a view really does

API

View

Tables

SELECT * FROM menu_today

1/5The API queries the view as if it were a normal table.

The big ideas

What you need to know

A saved query

A normal view stores only the SQL. Every SELECT on it runs that query fresh.

Hide complexity

Put the tricky joins in one view. App code and analysts query a simple name.

Limit what's visible

Grant access to a view without the salary column instead of the full table.

Materialized views

These store the result on disk for speed. Refresh them on a schedule; they don't update themselves.

See it in code

Walk through the code

sql

1CREATE VIEW menu_today AS2SELECT d.name, d.price, s.qty3FROM dishes d4JOIN stock s ON s.dish_id = d.id5WHERE s.qty > 0;6 7SELECT name, price FROM menu_today ORDER BY price;8 9-- Heavy report? Store the result and refresh it10CREATE MATERIALIZED VIEW sales_by_day AS11SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;12 13REFRESH MATERIALIZED VIEW sales_by_day;

1/4The view saves this join and filter under the name menu_today.

Normal views are always fresh but run a full query each time. Materialized ones are fast but only as fresh as the last refresh.

Save yourself the pain

Common mistakes

Oops

Expecting a normal view to make a slow query fast.

Do this instead

A view runs the same SQL underneath. Speed comes from indexes or a materialized view.

Oops

Stacking views on views on views.

Do this instead

Deep nesting hides what's really running. Keep it to one or two levels and check EXPLAIN.

Your mission

Build a canteen view

~20 min

Quick check

You update a row in the stock table. When does the normal view menu_today show the change?

Go deeper

1Create a view that joins a few tables into a simplified, reusable query.
2Learn the difference between a regular view and a materialized view.
3Understand that views don't store data themselves — they run the underlying query each time.