MENU


Views

A view is a virtual table created by referencing an existing table or another view. The view's definition is frozen at creation time, so changes made to the underlying tables afterward do not change the view's definition – for example, if a view references a table and new columns are added to that table later, those new columns do not become part of the view. New rows added to the underlying tables, however, do become visible through the view.

Basic Syntax

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

CREATE VIEW syntax. OR REPLACE replaces an existing view of the same name.


The select_statement that defines a view cannot: contain a subquery in the FROM clause; refer to system or user variables; refer to program parameters or local variables within a stored program; refer to prepared statement parameters; refer to a temporary table; or be associated with a trigger.

DEFINER and SQL SECURITY determine which account is used when checking access privileges. DEFINER means the required privileges must be held by the user who defined the view; INVOKER means they must be held by the user who invokes the view. As of MySQL 8.4, the SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER dynamic privileges give finer-grained control over which accounts may set an arbitrary or nonexistent account as a view's DEFINER, instead of requiring the broad SUPER privilege – see Privileges.

For an updatable view, WITH CHECK OPTION prevents inserts and updates to rows except those for which the WHERE clause in the select_statement is true. CASCADED (the default) causes checks for other underlying views to be evaluated as well; LOCAL restricts the CHECK OPTION to the view being defined.

ALTER
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  [DEFINER = user]
  [SQL SECURITY { DEFINER | INVOKER }]
  VIEW view_name [(column_list)]
  AS select_statement
  [WITH [CASCADED | LOCAL] CHECK OPTION]

ALTER VIEW alters an existing view; the syntax mirrors view creation.


DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]

Removes one or more views. RESTRICT and CASCADE are accepted but ignored.


The worked example below shows a view surviving new rows inserted after its creation, and a second example showing an updatable view created WITH LOCAL CHECK OPTION – inserting through the view also updates the underlying table.

Algorithms

With ALGORITHM=MERGE, the text of a statement referring to the view and the view's own definition are merged, so that parts of the view definition replace corresponding parts of the statement. MERGE is usually more efficient than TEMPTABLE. For example, given

CREATE ALGORITHM=MERGE VIEW vw(vc1,vc2) AS SELECT c1,c2 FROM tbl WHERE c3>10;

and the query SELECT c1,c2 FROM vw WHERE vc1<50;, the statement actually executed is SELECT c1,c2 FROM tbl WHERE (c3>10) and (c1<50);


With ALGORITHM=TEMPTABLE, the view's results are retrieved into a temporary table, which is then used to execute the statement. This allows locks on the underlying tables to be released once the temporary table has been built and before it is used to finish processing the statement. A TEMPTABLE view cannot be updated.

With ALGORITHM=UNDEFINED (the default), MySQL chooses which algorithm to use, preferring MERGE over TEMPTABLE.

Updatable Views

For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. A view is not updatable if it contains any of the following: aggregate functions; DISTINCT; GROUP BY; HAVING; UNION; a subquery in the SELECT list; certain joins; a nonupdatable view in the FROM clause; a subquery in the WHERE clause that refers to a table in the FROM clause; references only to literal values; ALGORITHM=TEMPTABLE; or multiple references to any column of a base table.

In the worked example, because product_details is updatable, an UPDATE issued against the view is allowed to modify the underlying products table, and the change is reflected in both the view and the table.

A view is additionally insertable if it satisfies these further conditions: there must be no duplicate view column names; the view must contain every column of the base table that lacks a default value; and the view columns must be simple column references, not derived expressions.

vs Temporary Tables

A TEMPORARY table is visible only within the current session and is dropped automatically when the session closes. If a complex query's results need to be reused repeatedly, a TEMPORARY table can improve performance by caching the intermediate results.

A VIEW, by contrast, persists across sessions. It behaves much like a query under the hood: every time a view is accessed, the SQL server regenerates the results from the base tables. Although this makes a VIEW slower in that sense, it guarantees the results returned are always up to date. See Table Definitions for TEMPORARY TABLE syntax and Database, Server, Plugin for CREATE DATABASE. 

DROP TABLE IF EXISTS tbl;
DROP VIEW IF EXISTS vw;
CREATE TABLE tbl (a INT, b INT);
INSERT INTO tbl VALUES (1,2),(3,4),(5,6);
CREATE VIEW vw AS SELECT a,b,a+b FROM tbl WHERE a>1;
INSERT INTO tbl VALUES (7,8);
SELECT * FROM vw;

aba+b
347
5611
7815

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (a INT, b INT);
INSERT INTO tbl VALUES (1,2),(3,4),(5,6);

CREATE OR REPLACE
    DEFINER = 'root'@'localhost'
    SQL SECURITY DEFINER
    VIEW vw(x,y)
    AS SELECT a,b FROM tbl WHERE a>1
    WITH LOCAL CHECK OPTION;

INSERT INTO vw VALUES (2,9); -- updatable view
SELECT * FROM tbl;           -- underlying table updated as well

ab
12
34
56
29

CREATE VIEW product_details AS
   SELECT products.product_name, categories.category_name, products.price
      FROM products JOIN categories
      ON products.category_id = categories.category_id;

UPDATE product_details
   SET price = 9.99
   WHERE product_name = 'Coffee Mug';
-- product_details is updatable, so this UPDATE modifies the underlying
-- products table, and the change is reflected in both the view and the table.

CREATE DATABASE Moonvalley_Secondary_School;
USE Moonvalley_Secondary_School;

CREATE TABLE test_results (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50),
   subject VARCHAR(20),
   score INT
);

INSERT INTO test_results(name,subject,score) VALUES
('John', 'Maths', 54),
('Owen', 'History', 95),
('John', 'Maths', 25),
('Jane', 'Chemistry', 88),
('John', 'Maths', 93),
('Jane', 'Maths', 74),
('John', 'History', 78),
('Jane', 'Maths', 57),
('Jane', 'Chemistry', 12),
('Owen', 'Chemistry', 39);

-- a TEMPORARY table survives even a DROP DATABASE, and disappears only
-- when the session closes
CREATE TEMPORARY TABLE temp_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    score DECIMAL(5,2),
    subject VARCHAR(20)
);

INSERT INTO temp_results (name, score, subject)
SELECT name, AVG(score), subject
FROM test_results
GROUP BY name, subject;

-- a VIEW instead recomputes its result from the base table every time
CREATE VIEW final_results AS
SELECT name, MAX(score) AS max_score, subject
FROM test_results
GROUP BY name, subject;

SELECT * FROM temp_results;
SELECT * FROM final_results;

idnamescoresubject
1John57.33Maths
2Owen95.00History
3Jane50.00Chemistry
4Jane65.50Maths
5John78.00History
6Owen39.00Chemistry
namemax_scoresubject
John93Maths
Owen95History
Jane88Chemistry
Jane74Maths
John78History
Owen39Chemistry