Views

A view is a saved query that you can use like a table. It stores the query, not the data — each time you select from it, the query runs against the current data.

Creating and using a view

CREATE VIEW order_summaries AS
SELECT
  o.id AS order_id,
  c.name AS customer,
  o.ordered_on,
  o.status,
  SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, o.ordered_on, o.status;

SELECT customer, COUNT(*) AS orders, SUM(total) AS spent
FROM order_summaries
WHERE status <> 'cancelled'
GROUP BY customer
ORDER BY spent DESC;

+-------------------+--------+--------+
|     customer      | orders | spent  |
+-------------------+--------+--------+
| Ada Lovelace      |      3 | 589.69 |
| Grace Hopper      |      1 | 583.98 |
| Margaret Hamilton |      1 |    241 |
| Linus Torvalds    |      1 |  234.5 |
| Alan Turing       |      1 |     89 |
+-------------------+--------+--------+

Anyone querying order_summaries gets correct totals without repeating the joins — and if the calculation ever changes, it changes in one place.

Why use views

• Simplify — hide complex joins behind a friendly name.
• Consistency — everyone uses the same definition of "order total" or "active customer".
• Security — grant access to a view exposing only some columns (for example, customers without emails), rather than to the whole table.

Changing and dropping views

DROP VIEW IF EXISTS order_summaries;

PostgreSQL and MySQL also support CREATE OR REPLACE VIEW.

Materialized views

A regular view reruns its query every time. A materialized view (PostgreSQL, Oracle; "indexed view" in SQL Server) stores the results, making reads fast, and is refreshed on demand (REFRESH MATERIALIZED VIEW). Use them for expensive reports that don't need to be up to the second.