Views

Save a query under a name so the complicated part is written once.

• Intermediate
• 10 min read
• 3 objectives

Before this lessonLesson 14: Creating Tables and Design

A view is a stored SELECT with a name. Querying it runs the underlying query; nothing is copied, so the data is never stale.

Creating and using one

CREATE VIEW paid_orders AS
SELECT o.id, c.name AS customer, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid';

SELECT * FROM paid_orders ORDER BY total DESC;

OK

id | customer | total
---+---------+------
10 | Ada      | 120
11 | Ada      | 35.5

From here on, paid_orders behaves like a table: you can filter it, join it, aggregate it, even build another view on it.

CREATE VIEW paid_orders AS
SELECT o.id, c.name AS customer, o.total
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid';

SELECT customer, COUNT(*) AS orders, SUM(total) AS spent
FROM paid_orders
GROUP BY customer
ORDER BY spent DESC;

OK

customer | orders | spent
---------+-------+------
Ada      | 2      | 155.5

A view always reflects the current data

Change a row in the base table and the view changes with it — there is no stored copy to refresh.

CREATE VIEW paid_orders AS
SELECT id, customer_id, total FROM orders WHERE status = 'paid';

SELECT COUNT(*) AS paid_now FROM paid_orders;

UPDATE orders SET status = 'paid' WHERE id = 12;

SELECT COUNT(*) AS paid_after_update FROM paid_orders;

OK

paid_now
---------
2

1 row affected.

paid_after_update
------------------
3

View, CTE or table?

• CTE — the step matters only inside this one query.
• View — several queries, reports or people need the same definition.
• Table — the result is expensive and slightly stale is acceptable; that is a materialized view or a scheduled job.

What a view does not do

• It does not make anything faster. The underlying query runs every time.
• Views stack, and a view over a view over a view hides real cost.
• Most views are read-only; writing through one is restricted and engine-specific.
• DROP VIEW name; removes it, and drops nothing of the data.

Key takeaways

• A view is a named query, not stored data.
• It always shows current rows, and costs what its query costs.
• Use it when more than one query needs the same definition.
• A materialized view trades freshness for speed — and is not available everywhere.

-- Write your solution here