00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Create View

Last updated: 6 min read

A view is a named, saved SELECT query that you can use like a table. It stores no data of its own. Every time you query it, MySQL runs the saved query against the current data. Views simplify complex queries, hide sensitive columns and give applications a stable interface even when tables change.

1. What is a View?

A view is a saved query, not a copy of dataTwo base tables, employees and departments, are joined by a stored SELECT named emp_directory. When you query the view, MySQL runs that SELECT against the current table data and returns the result.employeesidnamesalarydept1Usha68000102Varun52000203Wasim6100010departmentsdeptdept_name10Sales20SupportVIEWemp_directory(stored SELECT)Result when queriednamedept_nameUshaSalesVarunSupportWasimSalessalary is hidden from usersof the view
Figure 1: a view stores only its SELECT. Each time you query it, MySQL reads the latest data from the base tables.
  • Simplify: wrap a long join or calculation in a short name.
  • Secure: expose only some columns or rows to a user.
  • Stable interface: rename or split tables later while keeping the view's shape.
  • Always current: results reflect the base tables at query time.

2. Syntax

syntax.sql 
CREATE [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  [SQL SECURITY {DEFINER | INVOKER}]
  VIEW view_name [(column_list)]
  AS select_statement
  [WITH [CASCADED | LOCAL] CHECK OPTION];

3. A Simple View

simple-view.sql 
CREATE VIEW emp_directory AS
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
WHERE e.active = TRUE;

SELECT * FROM emp_directory ORDER BY name;

Result: 4 rows

namedept_name
UshaSales
VarunSupport
WasimSales
YaminiResearch

You can filter, sort and join a view just like a table:

query.sql 
SELECT name FROM emp_directory WHERE dept_name = 'Sales';

Result: 2 rows

name
Usha
Wasim

4. Views Always Show Current Data

live.sql 
INSERT INTO employees VALUES (6, 'Aditi', 'aditi@corp.example', 58000, 30, TRUE);
SELECT * FROM emp_directory WHERE dept_name = 'Research';

Result: 2 rows

namedept_name
YaminiResearch
AditiResearch

No refresh was needed: the view re-ran its query.

5. Views with Calculations and Column Names

Give view columns clear names with aliases or a column list:

aggregate-view.sql 
CREATE VIEW dept_payroll (department, headcount, total_salary, avg_salary) AS
SELECT d.dept_name, COUNT(e.emp_id), SUM(e.salary), ROUND(AVG(e.salary), 0)
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id AND e.active = TRUE
GROUP BY d.dept_name;

SELECT * FROM dept_payroll ORDER BY total_salary DESC;

Result: 3 rows

departmentheadcounttotal_salaryavg_salary
Research2148000.0074000
Sales2129000.0064500
Support152000.0052000

6. CREATE OR REPLACE and ALTER VIEW

Creating a view that already exists fails; CREATE OR REPLACE redefines it:

query.sql 
CREATE VIEW emp_directory AS SELECT name FROM employees;
Output 
ERROR 1050 (42S01): Table 'emp_directory' already exists
replace.sql 
CREATE OR REPLACE VIEW emp_directory AS
SELECT e.name, e.email, d.dept_name
FROM employees e JOIN departments d ON d.dept_id = e.dept_id
WHERE e.active = TRUE;

SELECT * FROM emp_directory WHERE dept_name = 'Support';

Result: 1 row

nameemaildept_name
Varunvarun@corp.exampleSupport

ALTER VIEW view_name AS select… does the same for an existing view, but fails if the view doesn't exist.

7. Updatable Views

A simple view (one table, no GROUP BY, DISTINCT, aggregates, UNION or sub-queries in the select list) can be used in UPDATE, INSERT and DELETE; the change goes to the base table:

updatable.sql 
CREATE VIEW support_staff AS
SELECT emp_id, name, salary, dept_id FROM employees WHERE dept_id = 20;

UPDATE support_staff SET salary = salary + 3000 WHERE name = 'Varun';
SELECT emp_id, name, salary FROM employees WHERE emp_id = 2;

Result: 1 row

emp_idnamesalary
2Varun55000.00

Aggregate views can't be updated:

query.sql 
UPDATE dept_payroll SET headcount = 10 WHERE department = 'Sales';
Output 
ERROR 1288 (HY000): The target table dept_payroll of the UPDATE is not updatable

8. WITH CHECK OPTION

Through support_staff you could insert someone into Sales, and the row would vanish from the view. WITH CHECK OPTION stops changes that don't satisfy the view's WHERE:

check-option.sql 
CREATE OR REPLACE VIEW support_staff AS
SELECT emp_id, name, salary, dept_id FROM employees WHERE dept_id = 20
WITH CHECK OPTION;

INSERT INTO support_staff VALUES (7, 'Bhoomi', 50000, 20);   -- allowed
INSERT INTO support_staff VALUES (8, 'Chirag', 50000, 10);   -- Sales: rejected
Output 
Query OK, 0 rows affected
Query OK, 1 row affected
ERROR 1369 (HY000): CHECK OPTION failed 'company.support_staff'

9. Using Views for Security

Give a user access to the view only, not the table, so salaries stay private:

grant-view.sql 
CREATE USER 'reception'@'localhost' IDENTIFIED BY 'Front#Desk26';
GRANT SELECT ON company.emp_directory TO 'reception'@'localhost';
as reception 
SELECT name, dept_name FROM emp_directory ORDER BY name LIMIT 3;

Result: 3 rows

namedept_name
AditiResearch
BhoomiSupport
UshaSales
as reception 
SELECT name, salary FROM employees;
Output 
ERROR 1142 (42000): SELECT command denied to user 'reception'@'localhost' for table 'employees'
By default a view runs with the privileges of its creator (SQL SECURITY DEFINER), which is why reception can read through it without rights on the tables. SQL SECURITY INVOKER checks the caller's privileges instead.

10. How MySQL Processes a View

ALGORITHMHow it worksNotes
MERGEThe view's SQL is merged into your queryFast; indexes on base tables are used; required for updatable views
TEMPTABLEThe view result is materialised into a temporary table firstNeeded for GROUP BY, DISTINCT, UNION; not updatable
UNDEFINED (default)MySQL picks MERGE when possibleUsually leave this
MySQL views are not materialized: they don't store results. A view over a slow query is just as slow. Index the base tables, or keep a summary table if you need pre-computed results.

11. Inspecting Views

query.sql 
SHOW FULL TABLES WHERE Table_type = 'VIEW';

Result: 3 rows

Tables_in_companyTable_type
dept_payrollVIEW
emp_directoryVIEW
support_staffVIEW
query.sql 
SELECT table_name, is_updatable, check_option, security_type
FROM information_schema.views
WHERE table_schema = 'company';

Result: 3 rows

TABLE_NAMEIS_UPDATABLECHECK_OPTIONSECURITY_TYPE
dept_payrollNONONEDEFINER
emp_directoryYESNONEDEFINER
support_staffYESCASCADEDDEFINER

12. Practice Exercises

Exercise 1: Create a view high_earners showing name and salary of active employees earning more than 60,000.
solution.sql 
CREATE VIEW high_earners AS
SELECT name, salary FROM employees WHERE active = TRUE AND salary > 60000;
SELECT * FROM high_earners ORDER BY salary DESC;

Result: 3 rows

namesalary
Yamini90000.00
Usha68000.00
Wasim61000.00
Exercise 2: Create a view listing each department with the name of its highest-paid employee.
solution.sql 
CREATE VIEW dept_top_earner AS
SELECT d.dept_name, e.name, e.salary
FROM employees e JOIN departments d ON d.dept_id = e.dept_id
WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE dept_id = e.dept_id);
SELECT * FROM dept_top_earner ORDER BY dept_name;

Result: 3 rows

dept_namenamesalary
ResearchYamini90000.00
SalesUsha68000.00
SupportVarun55000.00

13. Summary

  • CREATE VIEW name AS SELECT …; saves a query you can use like a table.
  • Views store no data; they always show the current data of the base tables.
  • CREATE OR REPLACE VIEW redefines a view; ALTER VIEW changes an existing one.
  • Simple single-table views are updatable; WITH CHECK OPTION keeps changes inside the view's filter.
  • Grant access to views instead of tables to hide sensitive columns.