By now you can write fairly long queries: joins across three tables, GROUP BY with HAVING, date logic. Imagine your manager asks for the same "department salary summary" every week, the HR app needs an employee directory without salaries, and five reports all repeat the same join. Copy-pasting that SQL everywhere is tiring and error-prone.
A view solves this. It lets you save a SELECT query under a name and then use that name as if it were a table.
A view is a named, stored SELECT query that behaves like a virtual table. It stores the query, not the data.
In this chapter we will create views, query them, see how they always show fresh data, update data through them, protect them with WITH CHECK OPTION, and learn their limits. All examples use the shared company_db sample database with real MySQL 8.4 output. Some examples change data, so run them on a practice copy of the database (as we did in the UPDATE & DELETE chapter), or undo the changes afterwards.
Why Do We Need Views?
Reason | Example |
|---|---|
Simplicity: hide a complex query behind a simple name | Reports run |
Security: show only some columns or rows | An intern can see names and emails but not salaries |
Consistency: one definition of a business rule | "High value order = amount above 3000" is written once, not in every report |
Stability: applications depend on the view, not on the table layout | If a table is split or renamed, you rewrite the view and the apps keep working |
The last point connects to an idea from DBMS theory: views give logical data independence. Users see the data through views, so the underlying tables can change without breaking them.
Creating a View
CREATE VIEW view_name AS
SELECT ...;Let us build an employee directory that shows each employee with their department name and location. Without a view, everyone who needs this must remember the join (and remember to use a LEFT JOIN so Ishita, who has no department, is not lost):
CREATE VIEW employee_directory AS
SELECT e.emp_id, e.name, e.email, d.dept_name, d.location
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id;Query OK, 0 rows affected (0.00 sec)"0 rows affected" is a hint: creating a view copies no data. Only the query text is saved. Now use it exactly like a table:
SELECT * FROM employee_directory;+--------+--------------+--------------------+-------------+-----------+
| emp_id | name | email | dept_name | location |
+--------+--------------+--------------------+-------------+-----------+
| 1 | Aarav Sharma | aarav@company.com | Engineering | Bengaluru |
| 2 | Priya Iyer | priya@company.com | Engineering | Bengaluru |
| 3 | Rohan Mehta | rohan@company.com | Engineering | Bengaluru |
| 4 | Sneha Kapoor | sneha@company.com | Sales | Mumbai |
| 5 | Vikram Singh | vikram@company.com | Sales | Mumbai |
| 6 | Ananya Das | ananya@company.com | Sales | Mumbai |
| 7 | Karan Patel | karan@company.com | HR | Delhi |
| 8 | Meera Nair | NULL | HR | Delhi |
| 9 | Arjun Reddy | arjun@company.com | Marketing | Pune |
| 10 | Ishita Gupta | ishita@company.com | NULL | NULL |
+--------+--------------+--------------------+-------------+-----------+
10 rows in set (0.00 sec)You can add WHERE, ORDER BY, LIMIT and everything else on top of a view:
SELECT name, dept_name
FROM employee_directory
WHERE location = 'Mumbai'
ORDER BY name;+--------------+-----------+
| name | dept_name |
+--------------+-----------+
| Ananya Das | Sales |
| Sneha Kapoor | Sales |
| Vikram Singh | Sales |
+--------------+-----------+
3 rows in set (0.00 sec)The person writing this query does not need to know there is a join underneath at all.
Where Do Views Live?
A view belongs to a database, just like a table, and shares the same namespace (you cannot have a table and a view with the same name). SHOW FULL TABLES tells them apart:
SHOW FULL TABLES;+----------------------+------------+
| Tables_in_company_db | Table_type |
+----------------------+------------+
| customers | BASE TABLE |
| departments | BASE TABLE |
| employee_directory | VIEW |
| employees | BASE TABLE |
| orders | BASE TABLE |
+----------------------+------------+
5 rows in set (0.01 sec)A real, data-holding table is called a base table. To see the stored definition of a view, use SHOW CREATE VIEW (the \G ending prints the result vertically, which is easier to read for long values):
SHOW CREATE VIEW employee_directory\G*************************** 1. row ***************************
View: employee_directory
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`tutor`@`localhost` SQL SECURITY DEFINER VIEW `employee_directory` AS select `e`.`emp_id` AS `emp_id`,`e`.`name` AS `name`,`e`.`email` AS `email`,`d`.`dept_name` AS `dept_name`,`d`.`location` AS `location` from (`employees` `e` left join `departments` `d` on((`d`.`dept_id` = `e`.`dept_id`)))
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)MySQL rewrites your query in a fully qualified form and adds three options we did not type: ALGORITHM, DEFINER and SQL SECURITY. We will look at each of them later in this chapter. (The DEFINER will show your own user name.)
A View Over an Aggregate Query
Views shine when they hide a GROUP BY. Here is the weekly department report as a view:
CREATE VIEW dept_salary_summary AS
SELECT d.dept_id, d.dept_name,
COUNT(e.emp_id) AS headcount,
SUM(e.salary) AS total_salary,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;SELECT * FROM dept_salary_summary ORDER BY dept_id;+---------+-------------+-----------+--------------+------------+
| dept_id | dept_name | headcount | total_salary | avg_salary |
+---------+-------------+-----------+--------------+------------+
| 10 | Engineering | 3 | 340000.00 | 113333.33 |
| 20 | Sales | 3 | 230000.00 | 76666.67 |
| 30 | HR | 2 | 118000.00 | 59000.00 |
| 40 | Marketing | 1 | 82000.00 | 82000.00 |
| 50 | Research | 0 | NULL | NULL |
+---------+-------------+-----------+--------------+------------+
5 rows in set (0.00 sec)Notice two things:
Every computed column has an alias (
headcount,avg_salary). Those aliases become the view's column names, so always name computed columns in a view. Otherwise you get columns calledCOUNT(e.emp_id), which are painful to use.We sorted when querying the view, not inside the view. A view is a virtual table, and like any table it has no guaranteed order. Put ORDER BY in the query that uses the view.
Now a filter on the summary that would otherwise need HAVING becomes a simple WHERE:
SELECT dept_name, headcount
FROM dept_salary_summary
WHERE headcount >= 2
ORDER BY avg_salary DESC;+-------------+-----------+
| dept_name | headcount |
+-------------+-----------+
| Engineering | 3 |
| Sales | 3 |
| HR | 2 |
+-------------+-----------+
3 rows in set (0.00 sec)A View Always Shows Current Data
Because a view stores the query and not the result, every time you read it, the query runs again against the current tables. Let us move Ishita (emp 10) into the empty Research department, then look at both views:
UPDATE employees SET dept_id = 50 WHERE emp_id = 10;
SELECT * FROM dept_salary_summary WHERE dept_id = 50;+---------+-----------+-----------+--------------+------------+
| dept_id | dept_name | headcount | total_salary | avg_salary |
+---------+-----------+-----------+--------------+------------+
| 50 | Research | 1 | 55000.00 | 55000.00 |
+---------+-----------+-----------+--------------+------------+
1 row in set (0.00 sec)SELECT name, dept_name FROM employee_directory WHERE emp_id = 10;+--------------+-----------+
| name | dept_name |
+--------------+-----------+
| Ishita Gupta | Research |
+--------------+-----------+
1 row in set (0.00 sec)We never touched the views, yet both reflect the change immediately. Put Ishita back before continuing:
UPDATE employees SET dept_id = NULL WHERE emp_id = 10;How MySQL Runs a View: MERGE vs TEMPTABLE
When you query a view, MySQL uses one of two strategies (the ALGORITHM we saw in SHOW CREATE VIEW):
MERGE: MySQL pastes the view's definition into your query and runs the combined query, as if you had written it by hand. Indexes on the base tables can be used normally.
TEMPTABLE: MySQL first runs the view's query into an internal temporary table, then runs your query against that.
ALGORITHM=UNDEFINED (the default) lets MySQL pick, and it prefers MERGE whenever it can. A view with GROUP BY, DISTINCT, aggregate functions, UNION or LIMIT cannot be merged, because, for example, filtering after grouping is not the same as filtering before.
We can watch merging with EXPLAIN (covered properly in the next chapter). First a simple view:
CREATE VIEW high_value_orders AS
SELECT order_id, customer_id, amount FROM orders WHERE amount > 3000;
EXPLAIN SELECT * FROM high_value_orders WHERE customer_id = 101;
SHOW WARNINGS;+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | customer_id | customer_id | 5 | const | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)The plan mentions only the base table orders, and the warning shows the query MySQL actually ran (shortened here):
select order_id, customer_id, amount from orders
where ((orders.customer_id = 101) and (orders.amount > 3000.00))The view's condition and our condition were merged into one WHERE clause. Now the aggregate view:
EXPLAIN SELECT * FROM dept_salary_summary WHERE dept_id = 10;+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | e | NULL | ref | dept_id | dept_id | 5 | const | 3 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)<derived2> and DERIVED show that this view was materialized into a temporary result (the TEMPTABLE way). MySQL 8 is smart enough to push our dept_id = 10 condition down into the grouped query, so only one department is computed, but in general, views over aggregates on big tables can be expensive. A view is not a cache: it does the full work every time.
Views do not make queries faster by themselves. They are a convenience and security tool. A query through a view costs roughly the same as the query written out by hand.
Views for Security: Hiding Columns
Salaries are confidential. We can create a view without the salary column:
CREATE VIEW employee_public AS
SELECT emp_id, name, email, dept_id, city FROM employees;
SELECT * FROM employee_public WHERE dept_id = 20;+--------+--------------+--------------------+---------+---------+
| emp_id | name | email | dept_id | city |
+--------+--------------+--------------------+---------+---------+
| 4 | Sneha Kapoor | sneha@company.com | 20 | Mumbai |
| 5 | Vikram Singh | vikram@company.com | 20 | Mumbai |
| 6 | Ananya Das | ananya@company.com | 20 | Kolkata |
+--------+--------------+--------------------+---------+---------+
3 rows in set (0.00 sec)SELECT salary FROM employee_public;ERROR 1054 (42S22): Unknown column 'salary' in 'field list'As far as the view is concerned, salary does not exist. The final step is permissions: an administrator gives the intern access to the view only, not to the table:
GRANT SELECT ON company_db.employee_public TO 'intern'@'localhost';The intern can now read the view, and MySQL reads the underlying employees table on their behalf. This works because of SQL SECURITY DEFINER (the default): the view runs with the privileges of the user who defined it. With SQL SECURITY INVOKER, it would run with the privileges of whoever is querying it, and the intern would need access to employees too.
Views can also hide rows, for example a view per region that only contains that region's customers.
Updating Data Through a View
Some views are updatable: you can run INSERT, UPDATE and DELETE on them, and MySQL applies the change to the base table. Roughly, a view is updatable when each row of the view maps to exactly one row of one base table. It is not updatable if it uses:
aggregate functions (
SUM,COUNT, ...) orGROUP BY/HAVINGDISTINCTUNION/UNION ALLcertain subqueries and most joins (especially outer joins)
only constants or computed expressions for the column you try to change
Why? Think about it: if you change headcount in the summary view from 3 to 5, which employees should MySQL invent? There is no sensible answer, so MySQL refuses:
UPDATE dept_salary_summary SET headcount = 5 WHERE dept_id = 10;ERROR 1288 (HY000): The target table dept_salary_summary of the UPDATE is not updatableLet us make a simple, updatable view of the Sales team:
CREATE VIEW sales_team AS
SELECT emp_id, name, dept_id, salary, hire_date
FROM employees
WHERE dept_id = 20;
UPDATE sales_team SET salary = salary + 5000 WHERE emp_id = 5;
SELECT emp_id, name, salary FROM employees WHERE emp_id = 5;+--------+--------------+----------+
| emp_id | name | salary |
+--------+--------------+----------+
| 5 | Vikram Singh | 67000.00 |
+--------+--------------+----------+
1 row in set (0.00 sec)The raise given through the view went straight into employees (62000 + 5000). A DELETE through the view can only touch rows the view can see:
DELETE FROM sales_team WHERE emp_id = 7; -- Karan is in HR, not SalesQuery OK, 0 rows affected (0.00 sec)Karan is safe, because the view's WHERE dept_id = 20 is added to the DELETE.
You can check which of your views are updatable in information_schema.VIEWS:
SELECT TABLE_NAME, IS_UPDATABLE, CHECK_OPTION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;+---------------------+--------------+--------------+
| TABLE_NAME | IS_UPDATABLE | CHECK_OPTION |
+---------------------+--------------+--------------+
| dept_salary_summary | NO | NONE |
| employee_directory | NO | NONE |
| employee_public | YES | NONE |
| high_value_orders | YES | NONE |
| sales_team | YES | NONE |
+---------------------+--------------+--------------+
5 rows in set (0.00 sec)The directory view is not updatable because of its outer join.
Inserting Through a View
An INSERT through a view fills only the view's columns; every other column of the base table gets its default value. If a hidden column is NOT NULL with no default, the insert fails. For example, employee_public does not include hire_date or salary, which are both NOT NULL:
INSERT INTO employee_public (emp_id, name, email, dept_id, city)
VALUES (12, 'Dev Malhotra', 'dev@company.com', 10, 'Pune');ERROR 1423 (HY000): Field of view 'company_db.employee_public' underlying table doesn't have a default valueThat is why sales_team includes salary and hire_date.
WITH CHECK OPTION: Keeping Rows Inside the View
Here is a surprising problem. Through sales_team, move Ananya to HR:
UPDATE sales_team SET dept_id = 30 WHERE emp_id = 6;
SELECT * FROM sales_team;+--------+--------------+---------+-----------+------------+
| emp_id | name | dept_id | salary | hire_date |
+--------+--------------+---------+-----------+------------+
| 4 | Sneha Kapoor | 20 | 110000.00 | 2017-11-20 |
| 5 | Vikram Singh | 20 | 67000.00 | 2021-03-08 |
+--------+--------------+---------+-----------+------------+
2 rows in set (0.00 sec)The update worked, and Ananya vanished from the very view we used to edit her. Similarly, someone could INSERT a Marketing employee through the Sales view. If the view is meant to let a Sales manager manage only Sales staff, this is a hole.
WITH CHECK OPTION closes it: any INSERT or UPDATE through the view must produce a row that the view's WHERE clause still accepts. Let us put Ananya back, then redefine the view with CREATE OR REPLACE VIEW:
UPDATE employees SET dept_id = 20 WHERE emp_id = 6;
CREATE OR REPLACE VIEW sales_team AS
SELECT emp_id, name, dept_id, salary, hire_date
FROM employees
WHERE dept_id = 20
WITH CHECK OPTION;Now the same moves are rejected:
UPDATE sales_team SET dept_id = 30 WHERE emp_id = 6;ERROR 1369 (HY000): CHECK OPTION failed 'company_db.sales_team'INSERT INTO sales_team (emp_id, name, dept_id, salary, hire_date)
VALUES (11, 'Tanvi Shah', 40, 60000, '2024-06-01');ERROR 1369 (HY000): CHECK OPTION failed 'company_db.sales_team'But a row that belongs in the view is accepted:
INSERT INTO sales_team (emp_id, name, dept_id, salary, hire_date)
VALUES (11, 'Tanvi Shah', 20, 60000, '2024-06-01');
SELECT * FROM sales_team;+--------+--------------+---------+-----------+------------+
| emp_id | name | dept_id | salary | hire_date |
+--------+--------------+---------+-----------+------------+
| 4 | Sneha Kapoor | 20 | 110000.00 | 2017-11-20 |
| 5 | Vikram Singh | 20 | 67000.00 | 2021-03-08 |
| 6 | Ananya Das | 20 | 58000.00 | 2022-06-30 |
| 11 | Tanvi Shah | 20 | 60000.00 | 2024-06-01 |
+--------+--------------+---------+-----------+------------+
4 rows in set (0.00 sec)Tanvi is now a real row in employees. Clean up with DELETE FROM sales_team WHERE emp_id = 11; and UPDATE employees SET salary = 62000 WHERE emp_id = 5;.
When views are built on other views, WITH CASCADED CHECK OPTION (the default when you just write WITH CHECK OPTION) checks the conditions of this view and all views beneath it; WITH LOCAL CHECK OPTION checks this view's own condition, and the conditions of underlying views only if those views have a check option of their own. Querying information_schema.VIEWS again would now show CASCADED for sales_team.
Changing and Dropping Views
Statement | What it does |
|---|---|
| Creates the view, or replaces it if it exists |
| Replaces the definition of an existing view |
| Removes the view (the base tables and their data are untouched) |
| No error if it does not exist (just a note) |
ALTER VIEW high_value_orders AS
SELECT order_id, customer_id, amount, status
FROM orders
WHERE amount > 3000;
SELECT * FROM high_value_orders;+----------+-------------+---------+-----------+
| order_id | customer_id | amount | status |
+----------+-------------+---------+-----------+
| 1002 | 102 | 4800.00 | Delivered |
| 1004 | 103 | 7300.00 | Delivered |
| 1006 | 102 | 3100.00 | Delivered |
| 1007 | 101 | 5600.00 | Pending |
+----------+-------------+---------+-----------+
4 rows in set (0.00 sec)Dropping a view never deletes data, because a view holds none. You cannot drop a view with DROP TABLE (MySQL answers "Unknown table"); use DROP VIEW.
Pitfalls: Views Depend on Their Tables
A Broken View
MySQL does not stop you from changing a table that a view uses. If the view needs a column that no longer exists, the view breaks only when someone queries it:
CREATE TABLE tmp_t (a INT, b INT);
CREATE VIEW tmp_v AS SELECT a, b FROM tmp_t;
ALTER TABLE tmp_t DROP COLUMN b;
SELECT * FROM tmp_v;ERROR 1356 (HY000): View 'company_db.tmp_v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use themBefore renaming or dropping columns, check which views use the table (search VIEW_DEFINITION in information_schema.VIEWS).
SELECT * Is Frozen at Creation Time
A view created with SELECT * does not pick up columns added to the table later:
CREATE TABLE products (id INT PRIMARY KEY, title VARCHAR(30));
INSERT INTO products VALUES (1, 'Pen'), (2, 'Notebook');
CREATE VIEW all_products AS SELECT * FROM products;
ALTER TABLE products ADD COLUMN price INT;
SELECT * FROM all_products;+----+----------+
| id | title |
+----+----------+
| 1 | Pen |
| 2 | Notebook |
+----+----------+
2 rows in set (0.00 sec)The table now has a price column but the view does not. SHOW CREATE VIEW all_products reveals why: MySQL expanded the * into the list `products`.`id`, `products`.`title` when the view was created. List the columns explicitly in views so that what you see is what you get.
Materialized Views (and Why MySQL Has None)
Some databases (PostgreSQL, Oracle) also have materialized views, which store the query result physically and refresh it on demand or on a schedule. They make heavy reports fast, at the cost of the data being possibly stale.
Feature | Normal view | Materialized view |
|---|---|---|
Stores | Only the query | The query and its result rows |
Freshness | Always current | As fresh as the last refresh |
Read speed | Same as running the query | Fast, like reading a table |
Disk space | Almost none | Like a table |
In MySQL 8.4 | Yes | No (emulate with a summary table refreshed by a scheduled job or triggers) |
View vs Table
Point | Table | View |
|---|---|---|
Stores data | Yes | No, only the SELECT |
Can be queried with SELECT | Yes | Yes |
INSERT/UPDATE/DELETE | Always | Only if the view is updatable |
Can have its own indexes | Yes | No (it uses the base tables' indexes) |
Removed with |
|
|
Common Mistakes
Mistake | What happens | Fix |
|---|---|---|
Thinking a view stores a copy of the data | Surprise when results change after base table updates | Remember: view = saved query |
Expecting a view to speed up a slow query | Same cost as the original query | Optimise the query or add indexes (next chapter) |
Computed columns without aliases | View columns named like | Always alias expressions |
Relying on ORDER BY inside a view | Order not guaranteed when the view is used in other queries | Sort in the outer query |
| New table columns never appear | List columns explicitly |
Updating through a view without CHECK OPTION | Rows can be moved out of or inserted outside the view |
|
Dropping or renaming a column used by a view | Error 1356 when the view is queried | Check dependent views first |
Interview Questions
What is a view?
A virtual table defined by a stored SELECT query. It holds no data of its own; each time it is queried, the query runs against the current base tables.
What are the advantages of views?
They simplify complex queries, restrict access to certain columns or rows (security), keep business logic in one place, and provide logical data independence so applications are shielded from table changes.
Can we update a view?
Yes, if it is updatable: each view row maps to one base table row, with no aggregates, GROUP BY, DISTINCT, UNION or (most) joins. Changes go to the base table.
What does WITH CHECK OPTION do?
It rejects INSERTs and UPDATEs through the view that would create rows not satisfying the view's WHERE condition, so rows cannot "escape" the view.
Does a view improve performance?
Not by itself. A normal view runs its query every time. Performance comes from indexes on the base tables or from materialized views (not available in MySQL).
What is the difference between a view and a materialized view?
A view stores only the query and is always current. A materialized view stores the result rows, is fast to read, but must be refreshed and can be stale.
Can a view be created from another view?
Yes. Views can be built on tables, other views, or a mix of both.
What happens to a view if its base table is dropped?
The view definition remains but becomes invalid; querying it gives error 1356 until the table is recreated or the view is changed.
Summary
A view is a saved SELECT used like a table:
CREATE VIEW name AS SELECT ....It stores no data, so it always reflects the current base tables, and it is not automatically faster.
Use views to simplify complex queries, hide sensitive columns/rows, and keep logic in one place.
MySQL runs a view by MERGE (inline the definition) or TEMPTABLE (materialize first, needed for aggregates/DISTINCT/UNION).
Simple single-table views are updatable; aggregate, DISTINCT, UNION and outer-join views are not.
WITH CHECK OPTIONstops changes that would move rows outside the view.Manage with
CREATE OR REPLACE VIEW,ALTER VIEW,DROP VIEW,SHOW CREATE VIEW.Avoid
SELECT *in views; it is frozen at creation time.
We saw a few EXPLAIN plans in this chapter that mentioned keys like PRIMARY and customer_id. In the next chapter, Indexes, we will learn what those keys are and how they make queries fast.