This MySQL Create View Tutorial Explains all about Creating a View in MySQL using Different Clauses & Examples. It also covers how to Drop & Manage Views:
In MySQL, view is a virtual table that enables us to have a look at the data in the table without blocking the table from being accessed by other programs and thereby helping us to avoid deadlock situations.
Views are created on top of one or more than one table that holds data. Before proceeding ahead, please note, that we are using MySQL version 8.0.
=> Click here for the complete MySQL tutorial series
Table of Contents:
MySQL Create View

Syntax:
CREATE [REPLACE] VIEW view_name AS SELECT col1, col2, ... FROM table_name WHERE condition;
Syntax Explanation:
- The syntax starts with the keyword “CREATE VIEW”, thereby informing the MySQL Server about the type of activity to be performed. This is a mandatory keyword and cannot be omitted.
- REPLACE is an optional parameter. We can use this while we are working on an existing view.
- Next comes the name of the view. It should be unique just like the names of tables and columns.
- Then, select the columns from the table. A view can have all the columns of the underlying table or only a selected few.
- Next comes the name of the table on which the view has to be performed. This is mandatory and cannot be omitted.
- Then comes the WHERE condition, that restricts or filters the number of target rows on which the CREATE VIEW action has to be applied. WHERE is also a keyword, but an optional one.
The WHERE clause is, however, significant. If not mentioned, or if the condition is not set correctly then either the whole table or non-required rows will be a part of the view.
MySQL Create View Example
Given below is a sample table created in MYSQL.
Schema Name: pacific
Table Name: employees
Column Names:
empNum: Holds integer values for the employee number.
lastName: Holds varchar values for the last name of the employee.
firstName: Holds varchar values for the first name of the employee.
email: Holds varchar values for the email ID of the employee.
deptNum: Holds varchar for department ID that an employee belongs to.
salary: Holds decimal values of salary for each employee.
start_date: Holds date values for the joining date of the employee.
Schema Name: pacific
Table Name: departments
Column Names:
deptNum: Holds varchar for the department ID within an organization.
city: Holds the name of the city in which the departments work from.
country: Holds the name of the country corresponding to the city.
bonus Holds the percentage value of the bonus.
MySQL Simple Create A View
Now let’s create a view on top of the above table.
Given below is the query.
The SELECT clause can be with specific column names or we can use “*”, to get all the columns. Once, we create a view, we cannot add or delete the columns. If we want to add or delete columns, then we will have to either create a new view or replace this existing view.
The output statement shows that the CREATE VIEW SQL statements have been executed successfully. It also says that no rows have been affected. This implies that when a new row is created it does not impact the data in the underlying table.
Now let’s query the view.
Query:
CREATE VIEW employees_view AS SELECT empNum, lastName, firstName, email, deptNum, salary, start_date FROM employees ; SELECT empNum, firstName, lastName, email, deptNum, salary, start_date FROM employees_view ;
ResultSet:
empNum | lastName | firstName | deptNum | salary | start_date | |
---|---|---|---|---|---|---|
1001 | Andrews | Jack | ja@gmail.com | 1 | 3182.7 | 0001-01-01 |
1002 | Schwatz | Mike | ms@gmail.com | 1 | 5304.5 | 0001-01-01 |
1003 | Langley | Margaret | margaret.langley@gmail.com | 2 | 8820 | 0001-01-01 |
1004 | Harera | Sandra | sh@gmail.com | 1 | 10609 | 0001-01-01 |
1005 | Lee | Peter | pl@gmail.com | 2 | 14332.5 | 0001-01-01 |
1006 | Keith | Jenny | jk@gmail.com | 2 | 16537.5 | 0001-01-01 |
1008 | Bailey | Oliver | oliver.bailey@gmail.com | 3 | 24494.4 | 0001-01-01 |
1009 | Beker | Harry | hb@gmail.com | 5 | 30645.6 | 0001-01-01 |
1011 | Hanks | Tom | th@gmail.com | NULL | 10100 | 0001-01-01 |
1012 | Luther | Martin | ml@gmail.com | 3 | 13000 | 0001-01-01 |
1014 | Murray | Keith | km@gmail.com | 1 | 25000 | 0001-01-01 |
1015 | Branson | John | jb@gmail.com | 2 | 15000 | 0001-01-01 |
1016 | Martin | Richard | rm@gmail.com | 4 | 5000 | 0001-01-01 |
1017 | Johnson | Eve | ej@gmail.com | 3 | 5500 | 2019-11-25 |
1018 | Bond | Nolan | nb@gmail.com | 2 | 15000 | 2019-09-13 |
Create View Using GROUP BY and ORDER BY
Let’s assume a scenario where we need to get the total amount of salary, department wise.
Given below is the query:
Rather than running this query in the table every-time, we need the information, it’s better to query the view as this will consume fewer database sources.
Now, let’s see the contents of our view. We will find how much each department is sending on its employees in terms of salaries.
Query:
CREATE VIEW salPerDept AS SELECT deptNum, sum(salary) FROM employees GROUP BY deptNum ORDER BY deptNum desc ; SELECT * FROM employees_view ;
Result Set:
deptNum | sum(salary) |
---|---|
5 | 30645.6 |
4 | 5000 |
3 | 42994.4 |
2 | 69690 |
1 | 44096.2 |
NULL | 10100 |
The NULL in deptNum indicates that an employee is not a part of any department but is on payrolls of the organization.
MySQL Create View Using JOIN
Let’s assume that we want to get data about the department, the city it’s based out of, and the bonus that is offered to its employees. We need this information for every employee.
To achieve this, we will be using the JOIN keyword to get data from departments and employees table.
Let’s have a look at the query and its output.
We can use different types of JOIN here, like, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN depending upon our requirement.
Query:
CREATE VIEW join_view AS SELECT A.empNum, A.lastName, A.firstName, A.deptNum, B.city, B.country, B.bonus FROM employees A INNER JOIN departments B USING (deptNum) ; SELECT * FROM join_view ;
Let’s now execute a SELECT query on this view to see the output:
Result Set:
empNum | lastname | firstName | deptNum | city | country | bonus |
---|---|---|---|---|---|---|
1001 | Andrews | Jack | 1 | New York | United States | 3 |
1002 | Schwatz | Mike | 1 | New York | United States | 3 |
1004 | Harera | Sandra | 1 | New York | United States | 3 |
1014 | Murray | Keith | 1 | New York | United States | 3 |
1003 | Langley | Margaret | 2 | Charlotte | United States | 5 |
1005 | Lee | Peter | 2 | Charlotte | United States | 5 |
1006 | Keith | Jenny | 2 | Charlotte | United States | 5 |
1015 | Branson | John | 2 | Charlotte | United States | 5 |
1018 | Bond | Nolan | 2 | Charlotte | United States | 5 |
1008 | Bailey | Oliver | 3 | Chicago | United States | 8 |
1012 | Luther | Martin | 3 | Chicago | United States | 8 |
1017 | Johnson | Eve | 3 | Chicago | United States | 8 |
1016 | Martin | Richard | 4 | London | England | 10 |
1009 | Beker | Harry | 5 | Berlin | Germany | 13 |
Create View Using Subquery
In this kind of create view statement, we try to fetch the value of a column where the clause is based on the output of a subquery. Say, we need to find all those employees whose salary is more than the average salary of the organization.
Let’s see the query:
Query:
CREATE VIEW avgSal_view AS SELECT empNum, lastName, firstName, salary FROM employees WHERE salary > ( SELECT avg(salary) FROM employees ) ; SELECT * FROM avgSal_view ;
Result Set:
empNum | lastName | firstName | salary |
---|---|---|---|
1005 | Lee | Peter | 14332.5 |
1006 | Keith | Jenny | 16537.5 |
1008 | Bailey | Oliver | 24494.4 |
1009 | Beker | Harry | 30645.6 |
1014 | Murray | Keith | 25000 |
1015 | Branson | John | 15000 |
1018 | Bond | Nolan | 15000 |
Managing Views
By using Alter View or Create/Replace view we can modify/ replace any view. Using ALTER VIEW explicitly for recompiling a view is not valid.
If you have ALTER ANY Table system privileges or if the view is in your schema then you can use ALTER VIEW Statement.
By using the Alter View query, we can locate the recompilation errors before run time. Using the ALTER VIEW statement can affect the other object/view that is depending on it. So, we can recompile a view after altering.
We can redefine a view by using Create View and can replace the existing view by the Replace view query.
For example, consider employees_view. Here we have the lastName and firstName columns along with a few others. Consider we have a requirement to merge the firstName and lastName columns.
Given below is the snapshot of the view before executing the ALTER query.
Now let’s execute the ALTER VIEW query to merge the firstName and lastName.
The query is given below:
Query:
ALTER VIEW employees_view AS SELECT empNum, CONCAT(firstName, ' ', lastName) AS empName, email, deptNum, salary, start_date FROM employees ;
Let’s see the content of the employees_view now:
If you can notice, we don’t have two separate columns for names. Instead, we just have one column that has both the first name and last name concatenated.
Learn more =>> MySQL CONCAT Function
Query:
SELECT * FROM employees_view;
Result Set:
empNum | empName | deptNum | salary | start_date | |
---|---|---|---|---|---|
1001 | Jack Andrews | ja@gmail.com | 1 | 3182.7 | 0001-01-01 |
1002 | Mike Schwatz | ms@gmail.com | 1 | 5304.5 | 0001-01-01 |
1003 | Margaret Langley | margaret.langley@gmail.com | 2 | 8820 | 0001-01-01 |
1004 | Sandra Harera | sh@gmail.com | 1 | 10609 | 0001-01-01 |
1005 | Peter Lee | pl@gmail.com | 2 | 14333 | 0001-01-01 |
1006 | Jenny Keith | jk@gmail.com | 2 | 16538 | 0001-01-01 |
1008 | Oliver Bailey | oliver.bailey@gmail.com | 3 | 24494 | 0001-01-01 |
1009 | Harry Beker | hb@gmail.com | 5 | 30646 | 0001-01-01 |
1011 | Tom Hanks | th@gmail.com | 10100 | 0001-01-01 | |
1012 | Martin Luther | ml@gmail.com | 3 | 13000 | 0001-01-01 |
1014 | Keith Murray | km@gmail.com | 1 | 25000 | 0001-01-01 |
1015 | John Branson | jb@gmail.com | 2 | 15000 | 0001-01-01 |
1016 | Richard Martin | rm@gmail.com | 4 | 5000 | 0001-01-01 |
1017 | Eve Johnson | ej@gmail.com | 3 | 5500 | 2019-11-25 |
1018 | Nolan Bond | nb@gmail.com | 2 | 15000 | 2019-09-13 |
Dropping A View
We can drop a view using the DROP VIEW statement. One can use a DROP VIEW if they have DROP ANY view privilege’s or else it should be in their schema. The drooping view does not impact the underlying table on which the view has been built or the data in that table.
Query:
DROP VIEW (view name);
FAQs
Q #1) How to create views in MySQL?
Answer: There are multiple ways to create views in MySQL. We have gone through four different ways as shown below.
1. MySQL Simple Create A View
2. MySQL Create View Using GROUP BY and ORDER BY
3. MySQL Create View Using JOIN
4. MySQL Create View Using Subquery
Q #2) What is a view in MySQL?
Answer: Views are like pre-executed queries stored in logical structure so that we don’t have to re-execute the complex queries on the table. We can just query the view and get our data without impacting the table.
Q #3) Can we apply JOIN on views while creating views?
Answer: Yes, JOIN statements could be used on views, in a similar way in which we use it on the tables.
Q #4) Can we create a view from another view?
Answer: Views essentially act as tables, but as a virtual one. Hence, the way we create views on top of a table, we can similarly create views on top of another view.
Q #5) Why do we use views?
Answer: As a result of the SQL query, the view creates a logical table. We use views for the following reasons.
- By using view, we can limit the visibility of rows and columns (using the Select and Where clause) for relevant tasks.
- Views are used when we are combining rows and columns (using Union and Join) from multiple tables.
- To get a more distinct presentation of aggregating rows (using Group by and Having) into a table with finer details.
- Views are used while renaming or decoding either column (using AS) or rows (using JOIN, IF, CASE, or Oracle’s DECODE).
- By combing any of the above with security settings, we can check if the user has access to what they are authorized only.
Conclusion
In this tutorial, we explored the different ways of creating a view in MySQL.
1. MySQL Simple Create A View
2. MySQL Create View Using GROUP BY and ORDER BY
3. MySQL Create View Using JOIN
4. MySQL Create View Using Subquery
We can choose either of these options depending upon the project requirements.
Further reading =>> MySQL CREATE TABLE
Happy Learning!!