Views

Some queries you write again and again. The shop calculates order totals in almost every report: join orders to order_items, multiply quantity by price, group by order. Copying that query into ten reports means ten places to fix when the rule changes.

A view is a saved SELECT with a name. Once you create it, you can query it just like a table. In this lesson you will create views, use them in joins, hide columns that some users should not see, and learn which views can be updated.

Creating a view

CREATE VIEW name AS is followed by any SELECT statement:

CREATE VIEW order_totals AS
SELECT o.order_id, o.customer_id, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.customer_id, o.order_date, o.status;

SELECT order_id, order_date, status, order_total
FROM order_totals
WHERE order_total > 8000
ORDER BY order_total DESC, order_id;

order_id | order_date | status    | order_total
---------+------------+-----------+------------
110      | 2025-04-01 | delivered | 32797
102      | 2025-01-12 | delivered | 15999
104      | 2025-02-02 | delivered | 8999
113      | 2025-05-02 | shipped   | 8999
112      | 2025-04-18 | delivered | 8695
(5 rows)

The second statement does not know or care that order_totals is a view. It filters and sorts it like any table. The join and the GROUP BY are hidden inside the view, written once.

A view does not store data. It stores only the query. Each time you select from the view, MySQL runs the saved query against the current tables. (Some databases also have "materialized views" that store results; MySQL does not.)

Views are always up to date

Because the query runs every time, a view always reflects the latest data. Here Priya's order 109 is changed from two books to three, and the view immediately shows the new total:

CREATE VIEW order_totals AS
SELECT o.order_id, o.customer_id, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.customer_id, o.order_date, o.status;

UPDATE order_items SET quantity = 3 WHERE item_id = 13;

SELECT order_id, order_total FROM order_totals WHERE order_id = 109;

order_id | order_total
---------+------------
109      | 1350
(1 row)

Two books at ₹450 were ₹900; three are ₹1,350. Nobody had to refresh the view. (In the browser the database resets on every run, so each example creates the view again. In MySQL you create it once and it stays until you drop it.)

Using views in bigger queries

A view can be joined, grouped and filtered like a table, which lets you build reports in layers. This finds the three customers who have spent the most, ignoring cancelled orders:

CREATE VIEW order_totals AS
SELECT o.order_id, o.customer_id, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.customer_id, o.order_date, o.status;

SELECT c.first_name, c.city, COUNT(*) AS orders, SUM(t.order_total) AS total_spent
FROM order_totals t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.status <> 'cancelled'
GROUP BY c.customer_id, c.first_name, c.city
ORDER BY total_spent DESC
LIMIT 3;

first_name | city   | orders | total_spent
-----------+--------+--------+------------
Sneha      | Delhi  | 1      | 32797
Priya      | Jaipur | 2      | 16899
Aarav      | Rohtak | 3      | 14944
(3 rows)

Without the view, this query would need a subquery or a second level of grouping. With it, the report reads almost like English.

Hiding columns with a view

Views are also a security tool. The employees table includes salaries, which most staff should not see. A view can show everything except the salary:

CREATE VIEW employee_directory AS
SELECT employee_id, first_name, last_name, department, job_title
FROM employees;

SELECT first_name, last_name, job_title
FROM employee_directory
WHERE department = 'Engineering'
ORDER BY employee_id;

first_name | last_name | job_title
-----------+-----------+--------------------
Neha       | Bansal    | Engineering Manager
Amit       | Chauhan   | Senior Developer
Pooja      | Saini     | Developer
Karan      | Dahiya    | Developer
(4 rows)

In MySQL you can then give a user permission to read employee_directory but not employees itself, so the salaries stay private. You will learn to do this in Users, Privileges & Backups. A view can also hide rows, for example a view of only Haryana customers for the Rohtak branch.

Changing and removing views

These statements manage views in MySQL:

-- Create the view, or replace it if it already exists
CREATE OR REPLACE VIEW employee_directory AS
SELECT employee_id, first_name, last_name, department, job_title, hire_date
FROM employees;

SHOW CREATE VIEW employee_directory;   -- see the saved query
DROP VIEW IF EXISTS employee_directory;

CREATE OR REPLACE VIEW is the easiest way to change a view, because it does not fail when the view exists. SQLite does not support OR REPLACE for views, so in the browser use DROP VIEW IF EXISTS followed by CREATE VIEW. Dropping a view never touches the data in the underlying tables.

Updatable views and WITH CHECK OPTION

In MySQL, a simple view over one table, without GROUP BY, DISTINCT, aggregate functions or UNION, is updatable: you can run UPDATE, INSERT or DELETE on the view and MySQL changes the base table. order_totals is not updatable, because each of its rows is a group of several order items.

WITH CHECK OPTION stops changes through the view that would make a row fall outside the view:

CREATE VIEW in_stock_products AS
SELECT product_id, product_name, price, stock
FROM products
WHERE stock > 0
WITH CHECK OPTION;

UPDATE in_stock_products SET price = 749.00 WHERE product_id = 1;  -- allowed
UPDATE in_stock_products SET stock = 0 WHERE product_id = 1;       -- refused: row would leave the view

The browser editor does not support updating views, so try these on MySQL.

Common mistakes

• Expecting a view to make a query faster. A view runs its saved query each time; it is exactly as fast as that query. Speed comes from good indexes on the base tables.
• Using SELECT * in a view. MySQL expands * into the column list when the view is created, so columns added to the table later do not appear in the view. List the columns you want.
• Sorting inside the view. Put ORDER BY in the query that uses the view, where the order is actually needed.
• Stacking many views on views. A view built on a view built on another view becomes hard to understand and debug. Two levels is usually plenty.

Key points

• A view is a named, saved SELECT that you query like a table.
• A view stores no data; it runs its query each time, so it is always up to date.
• Views keep a business rule, such as how to calculate an order total, in one place.
• Views can hide columns or rows, and users can be allowed to read a view but not its tables.
• CREATE OR REPLACE VIEW changes a view in MySQL; DROP VIEW removes it without touching the data.
• Simple single-table views are updatable; WITH CHECK OPTION keeps changes inside the view.

CREATE VIEW product_sales AS
SELECT p.product_id, p.product_name, p.category,
       SUM(oi.quantity) AS units_sold,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.product_name, p.category;

SELECT category, SUM(revenue) AS category_revenue
FROM product_sales
GROUP BY category
ORDER BY category_revenue DESC;