Views

A saved query with a name

Some queries are long and get used again and again: the order totals, the customer summary, the active products. Copy-pasting a ten-line join into every report is error-prone. A view saves the query under a name, and from then on you can select from it as if it were a table.

USE shop;
CREATE OR REPLACE VIEW v_order_totals AS
SELECT o.id AS order_id, o.customer_id, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.customer_id, o.order_date, o.status;

Now the complicated part is hidden:

SELECT * FROM v_order_totals WHERE total > 150 ORDER BY total DESC;

+----------+-------------+------------+---------+--------+
| order_id | customer_id | order_date | status  | total  |
+----------+-------------+------------+---------+--------+
|       12 |          10 | 2024-04-18 | shipped | 249.97 |
|       20 |           1 | 2024-07-19 | shipped | 218.74 |
|        4 |           3 | 2024-02-02 | shipped | 199.99 |
+----------+-------------+------------+---------+--------+

You can filter, sort, group and even join views with other tables. The view v_order_totals stores no rows. Each time you select from it, MySQL substitutes the saved query. If orders change, the view reflects it instantly.

SELECT status, COUNT(*) AS orders, ROUND(AVG(total), 2) AS average_total
FROM v_order_totals
GROUP BY status
ORDER BY status;

+-----------+--------+---------------+
| status    | orders | average_total |
+-----------+--------+---------------+
| pending   |      3 |         73.00 |
| shipped   |      4 |        184.42 |
| delivered |     11 |         92.33 |
| cancelled |      2 |         38.75 |
+-----------+--------+---------------+

Why use views

• Simplicity: give a complex join a simple name that analysts and applications can reuse.
• Consistency: the definition of "order total" lives in one place. Fix it once and every report is fixed.
• Security: expose only some columns or rows. A support team could query a view without the phone and email columns, with no access to the base table.
• Compatibility: after renaming or splitting a table, a view can keep the old shape so old code keeps working.

A view that hides columns:

CREATE OR REPLACE VIEW v_customers_public AS
SELECT id, first_name, city, country FROM customers;
SELECT * FROM v_customers_public WHERE country = 'India';

+----+------------+-----------+---------+
| id | first_name | city      | country |
+----+------------+-----------+---------+
|  1 | Asha       | Delhi     | India   |
|  6 | Farid      | Mumbai    | India   |
| 11 | Kavya      | Bengaluru | India   |
+----+------------+-----------+---------+

Changing and removing views

CREATE OR REPLACE VIEW redefines a view in one step, and ALTER VIEW does the same for an existing one. To see the saved definition:

SHOW CREATE VIEW v_customers_public\G

*************************** 1. row ***************************
                View: v_customers_public
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_customers_public` AS select `customers`.`id` AS `id`,`customers`.`first_name` AS `first_name`,`customers`.`city` AS `city`,`customers`.`country` AS `country` from `customers`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci

Remove a view with DROP VIEW IF EXISTS name;. Dropping a view does not touch the base tables or their data. To list views in a database, use SHOW FULL TABLES WHERE Table_type = 'VIEW';.

Updatable views

A simple view, based on a single table without grouping, DISTINCT or aggregates, can accept INSERT, UPDATE and DELETE, which pass through to the base table:

CREATE OR REPLACE VIEW v_indian_customers AS
SELECT id, first_name, last_name, email, city, country, signup_date
FROM customers
WHERE country = 'India'
WITH CHECK OPTION;

UPDATE v_indian_customers SET city = 'Gurugram' WHERE id = 1;
SELECT id, first_name, city FROM customers WHERE id = 1;

+----+------------+----------+
| id | first_name | city     |
+----+------------+----------+
|  1 | Asha       | Gurugram |
+----+------------+----------+

WITH CHECK OPTION guards the view's own rule. Without it, you could update a customer's country to something else through the view, and the row would vanish from the view. With it, MySQL refuses:

UPDATE v_indian_customers SET country = 'UK' WHERE id = 1;

ERROR 1369 (HY000): CHECK OPTION failed 'shop.v_indian_customers'

Views over joins, aggregates or UNION are generally read-only. v_order_totals cannot be updated.

What views do not do

• They are not faster. A view is just the query inside it. If the query is slow, the view is slow. Indexes on the base tables are what matter.
• They are not materialised. MySQL cannot store a view's result as a cached table. If you need that, create a real summary table and refresh it on a schedule.
• Deeply nested views are hard to understand and tune. A view that reads from a view that reads from a view makes EXPLAIN output confusing.
• They can hide cost. Selecting from a view of five joins looks as simple as selecting from a table, but it is not.

For the access-control use, views run with the rights of their definer by default (SQL SECURITY DEFINER), so the user of the view does not need rights on the base table. That is exactly what makes them useful for hiding columns, and exactly why you should define them carefully.

Cleaning up

The views above were created in your shop database. Remove them so the sample stays clean:

DROP VIEW IF EXISTS v_order_totals, v_customers_public, v_indian_customers;

Common mistakes

• Expecting a view to cache results.
• Using SELECT * inside a view definition. The column list is fixed when the view is created, so columns added later to the base table do not appear until you re-create the view.
• Stacking views on views until nobody understands the query.
• Forgetting WITH CHECK OPTION and letting rows disappear from an updatable view.
• Granting access to the view but leaving the base tables open.

Exercise

Create a view v_product_stock that shows each product's name, category name and a stock_status of 'out' when stock is 0, 'low' when below 50, otherwise 'ok'. Query the view for products that are not 'ok', then drop it.

CREATE OR REPLACE VIEW v_product_stock AS
SELECT p.id, p.name, c.name AS category,
       CASE WHEN p.stock = 0 THEN 'out' WHEN p.stock < 50 THEN 'low' ELSE 'ok' END AS stock_status
FROM products p
JOIN categories c ON c.id = p.category_id;

SELECT name, category, stock_status FROM v_product_stock WHERE stock_status <> 'ok' ORDER BY name;
DROP VIEW v_product_stock;

+-----------------------------+-------------+--------------+
| name                        | category    | stock_status |
+-----------------------------+-------------+--------------+
| Building Blocks Set         | Toys        | low          |
| Noise-Cancelling Headphones | Electronics | low          |
| Puzzle 1000 Pieces          | Toys        | out          |
| Standing Desk Mat           | Home        | low          |
+-----------------------------+-------------+--------------+