How to Create a View in MySQL: Best Practices and Tips

Looking for ways to simplify data access and enhance security? By creating views in MySQL, you can efficiently manage complex queries and control user access to sensitive data. Instead of working directly with raw tables, views offer a streamlined, consistent interface that helps businesses make the most of their data and make it more accessible with fewer risks. In this article, we’ll explore how to create view in MySQL, delve into the intricacies of MySQL CREATE VIEW syntax,  learn how to create or replace view in MySQL database, and explore some of the most common use cases to sophisticate your journey in database management. 

Table of contents

1. How to create a simple MySQL view with the CREATE VIEW statement
2. How to create a view with JOINs to combine data from multiple tables
3. How to update a MySQL view
4. How to drop a MySQL view
5. How to create a view in dbForge Studio for MySQL

What is a MySQL view?

A MySQL view is a virtual table that simplifies data access by storing a predefined query. It lets users retrieve data without directly accessing the underlying tables, essentially improving security and abstraction. With MySQL views, you can easily use simplified queries that encapsulate complex SQL logic and make data retrieval sleeker, restrict access to specific data, and limit its exposure to some of the team members, and boost overall readability. 

How to create a simple MySQL view with the CREATE VIEW statement

The basic view syntax in MySQL is as follows:

CREATE VIEW [db_name.]view_name [(column_list)]
AS
  select-statement;

• [db_name.] is the name of the database where your view will be created; if not specified, the view will be created in the current database
• view_name is a unique name of the view you are creating
• [(column_list)] defines the required list of columns that can be indicated in parentheses after the view name; by default, the list of columns is retrieved from the select list of the SELECT statement
• select-statement is a specified SELECT statement that can query data from tables or views

Here is the simplest example. If we have a table called customers in our current database, and we would like to request a list of customers with the transaction dates of their orders, the script may look as follows:

CREATE VIEW transactions AS
    SELECT 
        id_number,
        name,
        transaction_date
    FROM
        customers;

After we execute this statement, the transactions object will be available in Views. Now we can move on and execute a statement that selects all the fields in this view:

SELECT * FROM transactions;

The output will constitute a table containing three columns: id_number, name, and transaction_date.

How to create a view with JOINs to combine data from multiple tables

Our next example is somewhat more complicated since it involves multiple tables (there will be three in our case):

CREATE VIEW order_incomes AS
SELECT
    order_id,
    customer_name,
    SUM(ordered_quantity * product_price) total
FROM
    order_details
INNER JOIN orders USING (order_id)
INNER JOIN customers USING (customer_name)
GROUP BY order_id;

This view gives us information on order income per customer, grouped by order ID. For that purpose, we calculate the total income using the order_details table data and use the INNER JOIN clause to retrieve order IDs from the orders table and customer names from the customers table.

Updatable and insertable views

In MySQL, views can be classified as updatable or insertable, depending on the operations you want to perform on the underlying data. These types of views provide significant flexibility in how you manage data while offering a higher level of abstraction.

Updatable views

When you create view in MySQL, that is updatable, it allows you to change the underlying data using data manipulation statements (UPDATE, DELETE, and INSERT). The key requirement for a view to be updatable is that there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. If a view includes complex SQL constructs like GROUP BY, DISTINCT, or UNION, it may become non-updatable because the relationship between the view and the table becomes complex.

If you want to create an updatable view, make sure it doesn’t feature:

• Aggregate functions like SUM() or COUNT()
• DISTINCT or GROUP BY clauses
• Non-dependent subqueries in the select list

Additionally, ensure that the view references only a single table if it’s a join view.

Insertable views

An insertable view allows you to insert data into the underlying tables. For the view to be insertable, it must satisfy additional conditions:

• Reference simple columns, not expressions (e.g., col1 + 3).
• Feature only unique column names
• Include all columns of the base table that do not have default values
  

Even if a view is updatable, it might not necessarily be insertable. For example, when you create view in MySQL that contains an expression or derived column, it is not insertable because the expression cannot be directly mapped to a table column.

Restrictions on views in MySQL

While MySQL views offer powerful tools for simplifying queries and enhancing data abstraction, there are several restrictions to be aware of when working with them. These limitations impact the types of operations you can perform and the conditions under which a view can be used effectively.

Restriction	Explanation
Number of Tables in a View	A view can reference up to 61 tables. Exceeding this limit results in an error, so complex queries with multiple joins should be planned carefully.
View Processing and Indexing	Views cannot have their own indexes. They rely on indexes from underlying tables when using the merge algorithm. Views using the temptable algorithm do not benefit from indexing, which may lead to performance issues with large datasets.
Restrictions on Subqueries and Modifications	A view using the merge algorithm cannot modify a table that it selects from in a subquery. If processed with the temptable algorithm, this restriction is bypassed.
Invalidating Views	If an underlying table is altered or dropped, the view becomes invalid, but MySQL does not issue a warning until the view is queried. Use CHECK TABLE to verify view integrity.
Updatability Limitations	Not all views are updatable. Views containing aggregate functions, joins, or subqueries may not support INSERT, UPDATE, or DELETE.
Privileges and Backup Issues	Users with CREATE VIEW and SELECT privileges may not view the definition unless they also have SHOW VIEW. This affects backups using tools like mysqldump.
View Aliases and Length Limitations	Column aliases in a view cannot exceed 64 characters. Long aliases may cause replication or backup issues. Use shorter names to avoid errors.

How to update a MySQL view

If you need to update tables through views, you can use the INSERT, UPDATE, and DELETE statements to perform the corresponding operations with the rows of the underlying table. However, please note that in order to be updatable, your view must not include any of the following:

• Aggregate functions, e.g. MIN, MAX, COUNT, AVG, or SUM
• Such clauses as GROUP BY, DISTINCT, HAVING, UNION or UNION ALL
• Left or outer JOINs
• Multiple references to any column of the base table
• Subqueries in the SELECT or WHERE clause referring to the table appearing in the FROM clause
• References to non-updatable views in the FROM clause
• References to non-literal values

Now let’s create an updatable view called warehouse_details based on the warehouses table.

CREATE VIEW warehouse_details AS
   SELECT warehouse_id, phone, city
   FROM warehouses;

Now we can query data from this view:

SELECT * FROM warehouse_details;

Let’s say we want to change the phone number of the warehouse with the warehouse_id ’55’ through the warehouse_details view using the following UPDATE statement.

UPDATE warehouse_details 
SET 
    phone = '(555) 555-1234'
WHERE
    warehouse_id = 55;

Finally, we can check whether the change has been applied using the following query:

SELECT * FROM warehouse_details
WHERE
    warehouse_id = 55;

How to drop a MySQL view

If we no longer need a certain view, we can delete it with a simple DROP statement:

DROP VIEW warehouse_details;

The view WITH CHECK OPTION clause

The WITH CHECK OPTION clause in MySQL is a powerful feature that ensures data integrity when using updatable views. This clause restricts the rows that can be inserted or updated through a view, enforcing that only rows that satisfy the view’s SELECT statement can be modified. Let’s break down how the WITH CHECK OPTION clause works and its different configurations.

For demonstrational purposes, let’s create the following table with employee data (ID, department, and age columns):

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, department VARCHAR(50), age INT);

Now, let’s create a parent view without using the WITH CHECK OPTION, as follows:

CREATE VIEW dept_view ASSELECT * FROM employeesWHERE department = 'HR';

When working with it, you can insert into dept_view without any restrictions. 

Now, let’s imagine that you want HR representatives to insert some data into the table, but you don’t want them to be able to edit other department records. Thus, you can use the WITH CHECK OPTION to solve this case. When creating a new view (strong_dept_view), use the following WHERE condition:

CREATE VIEW strong_dept_view ASSELECT * FROM employeesWHERE department = 'HR'WITH CHECK OPTION;

As a result, through this view, it will be possible to insert only the values to HR department. The WITH CHECK OPTION checks the WHERE condition and lets you insert values only into rows that match it. 

LOCAL and CASCADED options

When you create view in MySQL defined in terms of another view, and you have parent and child views, the WITH CHECK OPTION clause can be configured with two different keywords: LOCAL and CASCADED. These keywords determine how MySQL applies the check across hierarchically related views.

Let’s explore how they are different. 

CASCADED CHECK OPTION

If you want the view users to make records only to the cells that match both the WHERE condition from the parent view as well as the condition from the child view that was derived from the parent one, you should opt for the CASCADED CHECK OPTION. 

For instance, let’s imagine that we have a child view that was derived from the parent view that had WHERE department = ‘HR’ condition applied:

CREATE VIEW cascaded_under_strong ASSELECT * FROM strong_dept_viewWHERE age <= 30WITH CASCADED CHECK OPTION;

As a result, you’ll enable editing only those cells that satisfy age <= 30 (child) and department = ‘HR’ (parent) requirements. 

For example, you’ll be able to insert value:

INSERT INTO cascaded_under_strong (department, age) VALUES ('HR', 28);

But these insertions don’t match both conditions, and thus they won’t be added:

INSERT INTO cascaded_under_strong (department, age) VALUES ('HR', 35);
INSERT INTO cascaded_under_strong (department, age) VALUES ('IT', 22);

Generally, WITH CHECK OPTION and WITH CASCADED CHECK OPTION work the same. If you do not specify a view that should have LOCAL CHECK OPTION, it’s treated as CASCADED by default.

Also, if your parent view has conditions without CHECK OPTION, and the CASCADED child view is applied to it, the CASCADED condition from the child view will still be applied to insertions, considering both parent and child view conditions. E.g., here’s the view:

CREATE VIEW cascaded_check_view AS
SELECT * FROM dept_view
WHERE age <= 30
WITH CASCADED CHECK OPTION;

This view will provide an option to insert the values to records that match both the department and age conditions: 

INSERT INTO cascaded_check_view (department, age) VALUES ('HR', 27)

However, these insertions will fail because both conditions are applied:

INSERT INTO cascaded_check_view (department, age) VALUES ('HR', 35);
INSERT INTO cascaded_check_view (department, age) VALUES ('IT', 28);

The child view will inherit the parent view condition (department = ‘HR’ ) and the insetrions will be checked against it as well as against the conditions of the child view.

LOCAL CHECK OPTION

WITH LOCAL CHECK OPTION checks only the condition that is stated in the child view, disregarding the additional parent view conditions if there was no CHECK OPTION. Let’s see how it works with our parent view example that featured WHERE department = ‘HR’. 

As you use the LOCAL CHECK OPTION in the script, and you have no CHECK OPTION for the parent view, you check only one condition (in our case, it’s going to be WHERE age <= 30):

CREATE VIEW local_check_view AS
SELECT * FROM dept_view
WHERE age <= 30
WITH LOCAL CHECK OPTION;

As a result, you will be able to insert values to every record that satisfies the age <= 30 condition, regardless of the department, e.g.,:

INSERT INTO local_check_view (department, age) VALUES ('Finance', 25);
INSERT INTO local_check_view (department, age) VALUES ('HR', 30);

However, when the records don’t meet the age condition, you’ll get an error:

INSERT INTO local_check_view (department, age) VALUES ('IT', 40);

However, in case we are going to use the parent view that has CHECK OPTION, the child view with the LOCAL CHECK OPTION will consider it. E.g., here’s the child view based on the strong_dept_view (already contains the WITH CHECK OPTION):

CREATE VIEW local_under_strong AS
SELECT * FROM strong_dept_view
WHERE age <= 30
WITH LOCAL CHECK OPTION;

As you try inserting values that meet both of the conditions, there are no errors:

INSERT INTO local_under_strong (department, age) VALUES ('HR', 25); 

However, whenever you try to insert values that do not satisfy either age or department conditions, there are going to be errors, e.g.,:

INSERT INTO local_under_strong (department, age) VALUES ('HR', 35); 
INSERT INTO local_under_strong (department, age) VALUES ('IT', 22);

As you can see, there is no difference in LOCAL CHECK OPTION and CSCADED CHECK OPTION applied to child view, if these views are based on the parent views with the CASCADED CHECK OPTION.

To sum it up, the WITH CHECK OPTION clause is an essential tool for ensuring data integrity when working with updatable views in MySQL. Restricting inserts and updates using LOCAL and CASCADED views helps limit inserts only those rows that meet the conditions of the view’s WHERE clause, preventing unintended changes to your data. 

Using CREATE OR REPLACE VIEW in MySQL

As you learn how to create views in MySQL, you might encounter the CREATE OR REPLACE VIEW statement. It allows you to update an existing view by defining a new query. This is useful when you need to modify a view’s structure or data but want to maintain the same view name.

Syntax for CREATE OR REPLACE VIEW

The basic VIEW syntax in MySQL for CREATE OR REPLACE VIEW is as follows:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

This command replaces an existing view with the same name if it exists or creates a new view if it doesn’t. 

To sum it up, to manage data more efficiently, we can use the CREATE OR REPLACE VIEW MySQL statement to update our existing views without dropping them first.

Common errors when creating views in MySQL

Creating views in MySQL can be a straightforward process, but there are still some common mistakes that can occur and disrupt your workflow. These errors typically stem from syntax issues, permission problems, or misunderstandings about how views interact with underlying tables. Let’s explore some of these common errors and how to troubleshoot them.

Common Error	Explanation	Example & Solution
Syntax Errors	Occur due to incorrect SQL structure, misplaced clauses, or missing keywords.	Error: ERROR 1064 (42000): You have an error in your SQL syntax. Solution: Ensure correct syntax.
Permission Issues	Happen when the user lacks CREATE VIEW or SELECT privileges.	Error: ERROR 1142 (42000): CREATE VIEW command denied. Solution: Grant necessary privileges (GRANT CREATE VIEW, SELECT ON database_name.* TO ‘user’@’localhost’;)
Non-Updatable Views	Views with aggregates, joins, or subqueries may not support UPDATE or INSERT.	Error: ERROR 1351 (HY000): View ‘database.view_name’ is not updatable.  Solution: Simplify the view structure or use triggers to handle updates.
Incorrect Column Aliases	Aliases may conflict with existing names, exceed 64-character limits, or reference unsupported data types.	Error: ERROR 1170 (42000): BLOB/TEXT column ‘column_name’ used in key specification without a key length. Solution: Use unique aliases within length limits and ensure proper data types.
Views with Invalid References	A view becomes invalid if a referenced table or view is dropped or altered.	Error: ERROR 1146 (42S02): Table ‘database_name.table_name’ doesn’t exist. Solution: Ensure all referenced tables/views exist or recreate/update the view.

Best practices for creating views in MySQL

Creating views in MySQL can simplify complex queries, and provide lots of advantages to business. However, there are still some essential tips that can help you ensure you handle the MySQL view the right way. Let’s explore them.

• Keep views straightforward. Simplify view definitions to enhance performance and readability.
• Enhance security with views. Use views to control access, exposing only necessary data while restricting direct table access.
• Keep views up to date. Regularly review and modify views to align with schema updates or business rule changes.
• Minimize nesting. Avoid layering views within views to prevent unnecessary performance overhead.
• Optimize for efficiency. Complex views can slow down queries, so, if possible, use indexes and refine underlying SQL for better performance.

How to create a view in dbForge Studio for MySQL

Now that we know the basic syntax, we need to find a tool that will help us manage our databases and views most effectively. We suggest you try dbForge Studio for MySQL, a toolset that covers nearly any operation with MySQL databases you can think of. Download a free trial, spend a couple of minutes installing it, and let’s get started.

Once you are connected to your MySQL database (look here to see how it is done), you can create a view using one of the two following ways.

The first way is writing and executing a query in a SQL document. Here dbForge Studio for MySQL delivers context-sensitive code completion, automatic syntax check, code snippets, quick navigation through large scripts, and customizable formatting profiles. In other words, you get every feature you might need in a single convenient IDE.

If you want to master this functionality with easy step-by-step guides, feel free to check the Writing and Executing SQL Statements section of our documentation. You will find everything there, from the creation of a new SQL document to the automated execution of your queries via the command-line interface.

The second way is one of the most notable tools of dbForge Studio — Query Builder. It presents your queries visually as diagrams, generates the abovementioned JOINs, and enables the interactive building of the INSERT, UPDATE, and DELETE statements to update your views.

Again, describing the workflow is more than this article can handle, but we have a special Query Builder section in our documentation, where you can get detailed guides to building and managing visual diagrams.

Conclusion

dbForge Studio for MySQL is an all-encompassing IDE for database development, management, and administration. It offers the easiest ways of building queries, comparing and analyzing data, developing and debugging stored procedures, comparing and syncing database schemas, and much more.

Get a 30-day free trial of dbForge Studio for MySQL today and see how irreplaceable it can become for your daily operations.

FAQ

How do I create a view in MySQL?

Use the CREATE VIEW statement to define a virtual table based on a SELECT query. Adjust the following syntax example with your database table values to create your first view:

CREATE VIEW view_name AS  
SELECT column1, column2 FROM table_name WHERE condition;

What is the CREATE VIEW command?

The CREATE VIEW command defines a virtual table (view) based on a SELECT query, allowing users to simplify queries and restrict direct table access.

What is the difference between a view and a table in MySQL?

A table stores data physically, while a view is a virtual representation of a query result. Views do not store data but reflect changes in underlying tables.

How can I create a view in MySQL using dbForge Studio for MySQL?

In dbForge Studio for MySQL, navigate Database in the app main menu and proceed to New Database Object. In the New Object modal window, choose the database from the Location list. Then, choose View from the Type list and proceed to the visual View editor.

How do I modify an existing MySQL view using CREATE OR REPLACE VIEW?

Use CREATE OR REPLACE VIEW to update a view’s definition without dropping and recreating it. Here’s the syntax example you can build upon to perform this action:

CREATE OR REPLACE VIEW view_name AS  
SELECT column1, column2 FROM table_name WHERE condition;

What will happen to the view if the table used in its selection query has been deleted?

The view becomes invalid. MySQL does not warn you in advance, but an error occurs when querying the view.

Can I create views in MySQL that include data from multiple tables?

Yes, you can use JOIN in a SELECT statement within the view definition to combine data from multiple tables.