Subscribe to MySQL Tutorial site RSS Feed
Skip Navigation Links » MySQL Views » Creating Views

Create SQL Views in MySQL

The syntax of creating a view in MySQL is as follows

   CREATE
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
   VIEW [database_name].  [view_name]
   AS 
   [SELECT  statement]

Algorithms

The algorithm attribute allows you to control which mechanism is used when creating a view.

  • MERGE means the input query will combine with the SELECT statement in the view definition and MySQL will execute the combined query to return the result set. This mechanism is more efficient than using TEMPTABLE (temporary table) but MERGE only allowed when the rows in the view represent a one-to-one relationship with the rows in the underlying table. In case the MERGE is not allowed, MySQL will switch the algorithm to UNDEFINED. The combination of input query and query in view definition into one query sometimes refers as view resolution.
  • TEMPTABLE means MySQL first create a temporary table based on SELECT statements in the view definition, and then it executes the input query against this temporary table. Because MySQL has to create temporary table to store the result set so it has to move the data from the real database table to the temporary table therefore TEMPTABLE mechanism is less efficient than MERGE. In addition, views which use TEMPTABLE algorithm is not updateable.
  • UNDEFINED is the default algorithm when you create a view without declaring explicit algorithm. UNDEFINED algorithm enables MySQL make a decision whether to use MERGE or TEMPTABLE. It prefers MERGE to TEMPTABLE.

View name

Each view is associated with a specific database therefore you can have database name prefix with the view name. View name is shared the same domain with tables so it cannot be the same name with existing tables or other views within a database.

SELECT statement

In SELECT statement, you can query any tables or views existed in the database. There are several rules which SELECT statement has to follows:

  • Subquery cannot be included in the SELECT statement.
  • Any variables such as local, user and session variables cannot be used in the SELECT statement.
  • The prepared statement cannot be used in the view.
  • Temporary tables or views cannot be used in the SELECT statements and any tables or views which referred by views must exists.
  • View cannot be associated with triggers.

Examples of creating view

Let’s take a look at the orderDetails table. We can create a view which represents however much sale per order. 

CREATE VIEW SalePerOrder
   AS 
   SELECT orderNumber,
   SUM  (quantityOrdered * priceEach) total
   FROM orderDetails
   GROUP by orderNumber
   ORDER BY total DESC

So each time when you want to know how much sale per order, you just execute the simple query:

SELECT total 
   FROM salePerOrder
   WHERE orderNumber = 10102

Here is an example of creating a view with join. This view returns order number, customer name and total money per order.

CREATE VIEW customerOrders 
   AS 
   SELECT  D.orderNumber,
   customerName,
   SUM(quantityOrdered * priceEach) total
   FROM orderDetails D
   INNER JOIN orders O ON O.orderNumber = D.orderNumber
   INNER JOIN customers C ON O.customerNumber =  C.customerNumber
   GROUP BY D.orderNumber
   ORDER BY total DESC

Here is another example of creating a view with subquery. This view returns all products which have price higher than average price of all products.

CREATE VIEW vwProducts
   AS 
   SELECT productCode,
   productName,
   buyPrice 
   FROM products
   WHERE buyPrice > (
   SELECT AVG  (buyPrice)
   FROM  products
   )
 
 ORDER BY buyPrice DESC