Database views: create, update, secure and when to use materialised views

Queries tend to grow. The report that started as SELECT * FROM orders ends up joining four tables, filtering cancelled rows and grouping by customer, and the same 15 lines get pasted into five places in the application. A view gives that query a name. It looks and behaves like a table when you SELECT from it, but it stores no data of its own: it is a saved query that runs whenever you use it. This post covers creating views, the three reasons to use them (simplicity, security, stability), the rules for updating through a view, what views cannot do (including why you cannot index one in MySQL), and the materialised-view alternative. Everything below was run on MySQL 8.4.

The sample data

CREATE TABLE customers (
  customer_id INT PRIMARY KEY, name VARCHAR(30), email VARCHAR(50),
  city VARCHAR(20), credit_card VARCHAR(20));
CREATE TABLE orders (
  order_id INT PRIMARY KEY, customer_id INT, order_date DATE,
  status VARCHAR(10), total DECIMAL(10,2));

INSERT INTO customers VALUES
  (1,'Asha','asha@example.com','Pune','4111-1111'),
  (2,'Ravi','ravi@example.com','Delhi','4222-2222'),
  (3,'Meena','meena@example.com','Pune','4333-3333');
INSERT INTO orders VALUES
  (1,1,'2026-01-05','paid',500),   (2,1,'2026-02-10','paid',700),
  (3,2,'2026-02-11','pending',300),(4,2,'2026-03-01','paid',900),
  (5,3,'2026-03-05','cancelled',100);

Creating and using a view

CREATE VIEW customer_sales AS
SELECT c.customer_id, c.name, c.city,
       COUNT(o.order_id)           AS orders,
       COALESCE(SUM(o.total), 0)   AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id, c.name, c.city;

Now the join, the filter and the grouping are hidden behind one name:

SELECT * FROM customer_sales ORDER BY revenue DESC;

+-------------+-------+-------+--------+---------+
| customer_id | name  | city  | orders | revenue |
+-------------+-------+-------+--------+---------+
|           1 | Asha  | Pune  |      2 | 1200.00 |
|           2 | Ravi  | Delhi |      1 |  900.00 |
|           3 | Meena | Pune  |      0 |    0.00 |
+-------------+-------+-------+--------+---------+

You can filter, join and aggregate a view like any table:

SELECT city, SUM(revenue) FROM customer_sales GROUP BY city;

which returned Pune 1,200.00 and Delhi 900.00. Behind the scenes MySQL combines your outer query with the view's stored query, so the work is done when you query it. A view never stores rows, so it is always current:

UPDATE orders SET status = 'paid' WHERE order_id = 3;
SELECT name, revenue FROM customer_sales WHERE customer_id = 2;

Ravi's revenue changed from 900.00 to 1,200.00 immediately, with no refresh step. Change a view's definition with CREATE OR REPLACE VIEW (or ALTER VIEW), see its definition with SHOW CREATE VIEW name, and remove it with DROP VIEW name. Dropping a view never touches the underlying data.

Why use views?

1. Simplicity and reuse

A view is the database's version of a function: write the complicated part once, name it, and reuse it. Reports, dashboards and application code select from customer_sales instead of repeating the join. If the logic changes (say "paid" should also include "refunded"), you fix it in one place.

2. Security: expose only some columns or rows

The customers table contains credit card numbers. Rather than granting access to the table, create a view that leaves out the sensitive column, and grant access to the view only:

CREATE VIEW customer_public AS
SELECT customer_id, name, city FROM customers;

SELECT * FROM customer_public;

+-------------+-------+-------+
| customer_id | name  | city  |
+-------------+-------+-------+
|           1 | Asha  | Pune  |
|           2 | Ravi  | Delhi |
|           3 | Meena | Pune  |
+-------------+-------+-------+

GRANT SELECT ON shop.customer_public TO 'support_user'@'%';

The support user sees names and cities but never email or credit_card. A view with a WHERE clause does the same for rows, for example showing a regional manager only their region. By default a MySQL view runs with SQL SECURITY DEFINER, which means it uses the creator's permissions, so the user needs rights on the view but not on the base table (this is what makes the pattern work). SQL SECURITY INVOKER checks the caller's permissions instead.

3. A stable interface

Applications can query a view while you reorganise the tables beneath it. Rename a column, split a table in two, then redefine the view to return the old shape, and old code keeps working while you migrate.

Updating data through a view

A simple view over a single table can be updated:

UPDATE customer_public SET city = 'Mumbai' WHERE customer_id = 3;
INSERT INTO customer_public (customer_id, name, city) VALUES (4, 'Kabir', 'Goa');

Both statements worked and changed the base table. The inserted row has NULL for the email and credit_card columns the view does not show, so the hidden columns must allow NULL or have defaults, or the insert fails. A view is updatable only when MySQL can map each view row to exactly one base-table row. These features make a view read-only:

• GROUP BY, HAVING, DISTINCT
• aggregate functions (SUM, COUNT...)
• UNION
• a subquery in the select list
• some joins (multi-table views can be updated in limited ways)

UPDATE customer_sales SET revenue = 0 WHERE customer_id = 1;

ERROR 1288 (HY000): The target table customer_sales of the UPDATE is not updatable

The information_schema.views table shows the answer for each view: customer_public and pune_customers have IS_UPDATABLE = YES; customer_sales has NO.

WITH CHECK OPTION

An updatable view with a WHERE clause has a loophole: you could update a row so that it no longer matches the filter and it would disappear from the view. WITH CHECK OPTION blocks that:

CREATE VIEW pune_customers AS
SELECT customer_id, name, city FROM customers
WHERE city = 'Pune'
WITH CHECK OPTION;

UPDATE pune_customers SET city = 'Goa' WHERE customer_id = 1;

ERROR 1369 (HY000): CHECK OPTION failed 'vw.pune_customers'

Asha cannot be moved out of Pune through this view. It also blocks inserting a non-Pune row. Without the option, the same statements would succeed.

What views cannot do

They do not make queries faster

A view is just a stored query, so the underlying work still happens each time. MySQL usually merges the view's query into the outer query (ALGORITHM=MERGE), but a view that uses GROUP BY or DISTINCT may be computed into a temporary table first (TEMPTABLE). A view over a slow query is a slow view. The speed comes from good indexes on the base tables, and you can check how a query on a view runs with EXPLAIN.

You cannot index a view in MySQL

Trying fails immediately:

CREATE INDEX ix ON customer_public (name);

ERROR 1347 (HY000): 'vw.customer_public' is not BASE TABLE

Index the base table columns that the view's WHERE, JOIN and GROUP BY use instead. Some other databases do support the idea: SQL Server has indexed views (a unique clustered index turns the view into stored data that the engine keeps up to date), which is often what older tutorials mean when they say "index a view".

They can break when the base changes

A view is stored text. If the underlying table or column is dropped or renamed, the view still exists but fails when used:

ERROR 1356 (HY000): View 'vw.v_tmp' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Check dependencies before dropping tables, and test views after schema changes. Also, SELECT * inside a view is expanded when the view is created, so new columns added later do not appear automatically; list columns explicitly in view definitions.

They are not a place for ORDER BY

An ORDER BY inside a view is not a reliable way to sort the result of queries on it; sort in the outer query.

Views versus other tools

Tool	Stores data?	Always current?	Best for
View	no, a saved query	yes	simplifying queries, security, a stable interface
Materialised view	yes, a stored result	only after refresh	expensive reports and aggregations read often
Temporary table	yes, per session	no, a snapshot	intermediate results in a procedure or script
CTE (WITH)	no	yes, within one statement	naming a step inside a single query
Summary table	yes	updated by your job	pre-computed totals you maintain yourself

Materialised views

A materialised view stores the result of its query on disk and refreshes it on demand or on a schedule. It trades freshness for speed. PostgreSQL and Oracle support it directly:

-- PostgreSQL
CREATE MATERIALIZED VIEW sales_by_month AS
SELECT date_trunc('month', order_date) AS month, SUM(total) AS revenue
FROM orders WHERE status = 'paid' GROUP BY 1;

REFRESH MATERIALIZED VIEW sales_by_month;

(This block is PostgreSQL syntax and was not run in my MySQL test.) MySQL has no materialised views. You emulate one with an ordinary summary table that a scheduled job rebuilds, for example a MySQL EVENT or a cron script running TRUNCATE and INSERT ... SELECT, or one that triggers keep up to date. Use this for dashboards that aggregate millions of rows, where yesterday's numbers are acceptable.

Good practice

1. Give views clear names (customer_sales, or a v_ prefix) so people know they are not tables.
2. List columns explicitly; avoid SELECT *.
3. Keep views simple. A view built on a view built on a view is hard to debug and slow.
4. Document why the view exists, especially security views.
5. Use WITH CHECK OPTION on updatable filtered views.
6. Grant access on views, not tables, for restricted users.
7. Review views when you change the schema.

Common interview questions

• What is a view? A virtual table defined by a stored SELECT; it holds no data.
• Can you insert, update or delete through a view? Yes for simple single-table views; not when it uses aggregates, GROUP BY, DISTINCT or UNION.
• What does WITH CHECK OPTION do? It rejects changes that would make a row disappear from the view.
• Do views improve performance? No. Materialised views can.
• What is the difference between a view and a materialised view? A view runs its query every time; a materialised view stores the result and must be refreshed.
• How do views help security? They expose only chosen columns and rows.

Next steps

Views often hide joins and subqueries. Continue with stored procedures and functions and transactions, and see how views fit the senior-level list in the top 20 SQL topics. The free MySQL course covers views on a real shop database.