SQL Tutorial › Performance & Objects › Views

SQL Views

Q: What is a view in SQL?

A virtual table defined by a SELECT query. It has a name and columns like a table but stores no data; its rows are computed when it is queried.

IntermediateLesson 62 of 6911 min read6 queries run on MySQL 8.4

A view is a saved SELECT with a name. You query it like a table, but it stores no data of its own: every time you read it, MySQL runs the underlying query on the current data. Views hide complex joins behind a simple name, give every report the same definition of “revenue”, and let you expose only some columns to some users.

In this lesson

Create and query a view
Change and drop views
Know when a view is updatable and what WITH CHECK OPTION does
Use views for simplicity and security

CREATE VIEW

An order summary that every report can reuse:

SQL

CREATE VIEW order_summary ASSELECT o.order_id, o.order_date, o.status, c.customer_name,       SUM(oi.quantity * p.price) AS order_totalFROM orders oJOIN customers c    ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_idJOIN products p     ON p.product_id = oi.product_idGROUP BY o.order_id, o.order_date, o.status, c.customer_name; SELECT * FROM order_summary WHERE status = 'Delivered' ORDER BY order_total DESC;

ResultMySQL 8.4

mysql> CREATE VIEW order_summary AS

Query OK, 0 rows affected

mysql> SELECT * FROM order_summary WHERE status = 'Delivered' ORDER BY order_…

order_id	order_date	status	customer_name	order_total
1002	2025-01-17	Delivered	Rohit Kumar	8999
1001	2025-01-05	Delivered	Ananya Sharma	4744
1003	2025-02-02	Delivered	Ananya Sharma	4298
1009	2025-05-15	Delivered	Fatima Khan	3499
1005	2025-03-08	Delivered	David Fernandes	3286
1010	2025-06-01	Delivered	Ishita Bose	1145
1007	2025-04-10	Delivered	Rohit Kumar	799

7 rows in set

How this query works, step by step

CREATE VIEW order_summary AS SELECT ... saves the query under a name. No rows are copied.
SELECT * FROM order_summary WHERE ... reads it like a table.
MySQL combines the view’s query with your WHERE and ORDER BY and runs them on the live tables.

A view always shows current data

SQL

CREATE VIEW low_stock ASSELECT product_id, product_name, stock FROM products WHERE stock < 20;SELECT * FROM low_stock;UPDATE products SET stock = 5 WHERE product_id = 2;SELECT * FROM low_stock;

ResultMySQL 8.4

mysql> CREATE VIEW low_stock AS

Query OK, 0 rows affected

mysql> SELECT * FROM low_stock

product_id	product_name	stock
3	USB-C Hub	0
4	Office Chair	12
5	Standing Desk	5
9	Monitor Arm	18

4 rows in set

mysql> UPDATE products SET stock = 5 WHERE product_id = 2

Query OK, 1 row affected

mysql> SELECT * FROM low_stock

product_id	product_name	stock
2	Mechanical Keyboard	5
3	USB-C Hub	0
4	Office Chair	12
5	Standing Desk	5
9	Monitor Arm	18

5 rows in set

How this query works, step by step

The first read lists the products with fewer than 20 in stock.
After the keyboard’s stock drops to 5, the same view includes it, without any change to the view.

Change and drop a view

MySQL

CREATE VIEW low_stock AS SELECT product_id, product_name, stock FROM products WHERE stock < 20;CREATE OR REPLACE VIEW low_stock ASSELECT product_id, product_name, category, stock FROM products WHERE stock < 10;SELECT * FROM low_stock;DROP VIEW low_stock;

ResultMySQL 8.4

mysql> CREATE VIEW low_stock AS SELECT product_id, product_name, stock FROM p…

Query OK, 0 rows affected

mysql> CREATE OR REPLACE VIEW low_stock AS

Query OK, 0 rows affected

mysql> SELECT * FROM low_stock

product_id	product_name	category	stock
3	USB-C Hub	Electronics	0
5	Standing Desk	Furniture	5

2 rows in set

mysql> DROP VIEW low_stock

Query OK, 0 rows affected

SHOW FULL TABLES WHERE Table_type = 'VIEW'; lists the views in a database, and SHOW CREATE VIEW name; shows a view’s query.

Updatable views

A simple view over one table, without GROUP BY, DISTINCT, aggregates or UNION, is updatable: changes go straight to the base table.

MySQL

CREATE VIEW furniture ASSELECT product_id, product_name, price, stock FROM products WHERE category = 'Furniture';UPDATE furniture SET price = price - 500 WHERE product_id = 4;SELECT product_id, product_name, price FROM products WHERE product_id = 4;

ResultMySQL 8.4

mysql> CREATE VIEW furniture AS

Query OK, 0 rows affected

mysql> UPDATE furniture SET price = price – 500 WHERE product_id = 4

Query OK, 1 row affected

mysql> SELECT product_id, product_name, price FROM products WHERE product_id …

product_id	product_name	price
4	Office Chair	8499

1 row in set

A view with an aggregate is not:

MySQL

CREATE VIEW category_stock AS SELECT category, SUM(stock) AS units FROM products GROUP BY category;UPDATE category_stock SET units = 0 WHERE category = 'Furniture';

ResultMySQL 8.4

mysql> CREATE VIEW category_stock AS SELECT category, SUM(stock) AS units FRO…

Query OK, 0 rows affected

mysql> UPDATE category_stock SET units = 0 WHERE category = 'Furniture'

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

WITH CHECK OPTION

Without it, you can insert rows through a view that the view itself will not show. WITH CHECK OPTION refuses them:

MySQL

CREATE VIEW furniture ASSELECT product_id, product_name, category, price, stock FROM products WHERE category = 'Furniture'WITH CHECK OPTION;INSERT INTO furniture VALUES (20, 'Bookshelf', 'Furniture', 4999, 8);INSERT INTO furniture VALUES (21, 'Stapler', 'Stationery', 299, 40);

ResultMySQL 8.4

mysql> CREATE VIEW furniture AS

Query OK, 0 rows affected

mysql> INSERT INTO furniture VALUES (20, 'Bookshelf', 'Furniture', 4999, 8)

Query OK, 1 row affected

mysql> INSERT INTO furniture VALUES (21, 'Stapler', 'Stationery', 299, 40)

ERROR 1369 (HY000): CHECK OPTION failed 'company.furniture'

How this query works, step by step

The bookshelf is Furniture, matches the view’s WHERE, and is inserted.
The stapler is Stationery, which the view would not show, so WITH CHECK OPTION rejects it.

Views for security

Give an analyst access to staff names and departments but not salaries:

MySQL

CREATE VIEW staff_directory ASSELECT emp_id, first_name, last_name, dept_id, city FROM employees; GRANT SELECT ON company.staff_directory TO 'analyst'@'localhost';

The analyst can query staff_directory but has no privilege on employees itself. See GRANT.

View vs table

	Table	View
Stores data	yes	no (only the query)
Always current	yes	yes, computed on every read
Can have its own indexes	yes	no, it uses the base tables’ indexes
Speed	direct	same as running the query

MySQL has no materialized views. To store a precomputed result, write it into a summary table (for example with CREATE TABLE ... AS SELECT) and refresh it on a schedule.

Quick quiz

1. Does a view store a copy of the data?

2. Which view can be updated?

3. What does WITH CHECK OPTION prevent?

Views FAQ

What is a view in SQL?

A virtual table defined by a SELECT query. It has a name and columns like a table but stores no data; its rows are computed when it is queried.

What is the difference between a view and a table?

A table stores data. A view stores a query and shows the current result of that query each time you read it.

Can we insert data into a view?

Yes, if the view is updatable: based on one table, with no aggregates, GROUP BY, DISTINCT or UNION. The rows go into the base table.

Does MySQL support materialized views?

No. Use a summary table refreshed by a scheduled event or batch job instead.