MySQL Create View Tutorial With Code Examples

By Sruthy

By Sruthy

Sruthy, with her 10+ years of experience, is a dynamic professional who seamlessly blends her creative soul with technical prowess. With a Technical Degree in Graphics Design and Communications and a Bachelor’s Degree in Electronics and Communication, she brings a unique combination of artistic flair…

Learn about our editorial policies.
Updated February 4, 2025
Edited by Kamila

Edited by Kamila

Kamila is an AI-based technical expert, author, and trainer with a Master’s degree in CRM. She has over 15 years of work experience in several top-notch IT companies. She has published more than 500 articles on various Software Testing Related Topics, Programming Languages, AI Concepts,…

Learn about our editorial policies.

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

MySQL Create View

Create View in MySQL

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.

MySQL Create view - example

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.

Create View Example

MySQL Simple Create A View

Now let’s create a view on top of the above table.

Given below is the query.

MySQL Simple Create A View

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.

Create A View - Query

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:

empNumlastNamefirstNameemaildeptNumsalarystart_date
1001AndrewsJackja@gmail.com13182.70001-01-01
1002SchwatzMikems@gmail.com15304.50001-01-01
1003LangleyMargaretmargaret.langley@gmail.com288200001-01-01
1004HareraSandrash@gmail.com1106090001-01-01
1005LeePeterpl@gmail.com214332.50001-01-01
1006KeithJennyjk@gmail.com216537.50001-01-01
1008BaileyOliveroliver.bailey@gmail.com324494.40001-01-01
1009BekerHarryhb@gmail.com530645.60001-01-01
1011HanksTomth@gmail.comNULL101000001-01-01
1012LutherMartinml@gmail.com3130000001-01-01
1014MurrayKeithkm@gmail.com1250000001-01-01
1015BransonJohnjb@gmail.com2150000001-01-01
1016MartinRichardrm@gmail.com450000001-01-01
1017JohnsonEveej@gmail.com355002019-11-25
1018BondNolannb@gmail.com2150002019-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:

MySQL Create View Using GROUP BY and ORDER BY

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.

Contents of the view

Query:

CREATE VIEW salPerDept AS
SELECT deptNum, sum(salary)
 FROM employees
GROUP BY deptNum
ORDER BY deptNum desc
;

SELECT *
 FROM employees_view
;

Result Set:

deptNumsum(salary)
530645.6
45000
342994.4
269690
144096.2
NULL10100

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.

MySQL Create View Using JOIN

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:

SELECT query

Result Set:

empNumlastnamefirstNamedeptNumcitycountrybonus
1001AndrewsJack1New YorkUnited States3
1002SchwatzMike1New YorkUnited States3
1004HareraSandra1New YorkUnited States3
1014MurrayKeith1New YorkUnited States3
1003LangleyMargaret2CharlotteUnited States5
1005LeePeter2CharlotteUnited States5
1006KeithJenny2CharlotteUnited States5
1015BransonJohn2CharlotteUnited States5
1018BondNolan2CharlotteUnited States5
1008BaileyOliver3ChicagoUnited States8
1012LutherMartin3ChicagoUnited States8
1017JohnsonEve3ChicagoUnited States8
1016MartinRichard4LondonEngland10
1009BekerHarry5BerlinGermany13

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:

MySQL Create View Using Subquery
MySQL Create View Using Subquery - Result Grid

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:

empNumlastNamefirstNamesalary
1005LeePeter14332.5
1006KeithJenny16537.5
1008BaileyOliver24494.4
1009BekerHarry30645.6
1014MurrayKeith25000
1015BransonJohn15000
1018BondNolan15000

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.

Managing Views

Now let’s execute the ALTER VIEW query to merge the firstName and lastName.

The query is given below:

ALTER VIEW query to merge firstName and lastName

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:

content of the employees_view

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:

empNumempNameemaildeptNumsalarystart_date
1001Jack Andrewsja@gmail.com13182.70001-01-01
1002Mike Schwatzms@gmail.com15304.50001-01-01
1003Margaret Langleymargaret.langley@gmail.com288200001-01-01
1004Sandra Harerash@gmail.com1106090001-01-01
1005Peter Leepl@gmail.com2143330001-01-01
1006Jenny Keithjk@gmail.com2165380001-01-01
1008Oliver Baileyoliver.bailey@gmail.com3244940001-01-01
1009Harry Bekerhb@gmail.com5306460001-01-01
1011Tom Hanksth@gmail.com101000001-01-01
1012Martin Lutherml@gmail.com3130000001-01-01
1014Keith Murraykm@gmail.com1250000001-01-01
1015John Bransonjb@gmail.com2150000001-01-01
1016Richard Martinrm@gmail.com450000001-01-01
1017Eve Johnsonej@gmail.com355002019-11-25
1018Nolan Bondnb@gmail.com2150002019-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);
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!!

Was this helpful?

Thanks for your feedback!

Leave a Comment