A database view is a virtual or logical table which is comprised of a SELECT query. Much like a database table, a view also consists of rows and columns that you can query against. Most database management systems, including MySQL, even allow you to update data in the underlying tables through the view, but with some caveats. In today's blog, we'll learn what a view is and how to create one for MySQL 8 using Navicat Premium as our client.
Basic Syntax
In MySQL, you use the CREATE VIEW statement to create a new view. Here is the basic syntax:
CREATE
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW view_name [(column_list)]
AS
select-statement;
Now, let's examine the syntax in more detail.
View Processing Algorithms
The ALGORITHM attribute tells MySQL which mechanism to use when creating the view. MySQL provides three algorithms: MERGE, TEMPTABLE, and UNDEFINED:
- The MERGE algorithm combines the input query with the SELECT statement, which defines the view, into a single query. MySQL then executes the combined query to return the merged result set. The MERGE algorithm cannot be applied to SELECT statements that contain aggregate functions such as MIN, MAX, SUM, COUNT, AVG or DISTINCT, GROUP BY, HAVING, LIMIT, UNION, and UNION ALL. If the MERGE algorithm cannot be applied, MySQL automatically changes the algorithm to UNDEFINED.
- The TEMPTABLE algorithm first creates a temporary table based on the SELECT statement that defines the view, and then it executes the input query against this temporary table. Because MySQL has to create a temporary table to store the result set and moves the data from the base tables to the temporary table, the TEMPTABLE algorithm is less efficient than the MERGE algorithm.
- UNDEFINED is the default algorithm when you create a view without specifying an explicit algorithm. The UNDEFINED algorithm lets MySQL make a choice of using MERGE or TEMPTABLE algorithm. MySQL chooses the MERGE algorithm first, due to its greater efficiency, but falls back to the TEMPTABLE algorithm if MERGE cannot be employed.
View Name
You can choose whatever name you wish for your view, so long as you follow the same naming rules as for tables. Moreover, views and tables share the same namespace within the database, so you can't give your view the same name as any existing table of view.
SELECT Statement
In the SELECT statement you can query data from any table or view that exists in the database. However, there are a few rules that the SELECT statement adhere to:
- The SELECT statement may contain a subquery in the WHERE clause but not in the FROM clause.
- The SELECT statement cannot refer to any variables including local variables, user variables, and session variables.
- The SELECT statement cannot refer to the parameters of prepared statements.
Creating a View in Navicat
In Navicat, you can create a new view by clicking the View button on the main toolbar and then clicking "New view" on the Objects toolbar:
The Definition tab is where you write your SQL. You can even use the View Builder to help write your statement!
The Algorithm can be found on the Advanced tab, along with a few other options:
Once you're done, you can test your View using the Preview button and then save it by clicking on Save.
Conclusion
Views are a great way to combine data from one or more tables in a format that you can query, but keep in mind that there are some disadvantages of using database views. For one, querying data from a database view can be slow - especially if the view is created based on other views. Also, you have to remember to change the view whenever you change the structure of a table that your view refers to.
