Course: MySQL Training
Topic: Managing Views Using ALTER VIEW and CREATE OR REPLACE VIEW
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
In MySQL, a view is a named SQL query that presents data from one or more underlying tables as a virtual table. Views help simplify complex queries, standardize reporting logic, and provide controlled access to selected data.
After creating a view, business requirements may change. You might need to add a column, introduce a filter, change a calculation, rename an output column, or modify the view's security settings. MySQL provides statements such as ALTER VIEW and CREATE OR REPLACE VIEW to manage these changes.
Both statements can change the definition of an existing view, but they differ in how they handle a view that does not already exist.
These statements are useful when maintaining database applications, employee management systems, reporting solutions, and business intelligence workflows.
After completing this topic, you will be able to:
ALTER VIEW and CREATE OR REPLACE VIEW.Before modifying a view, it is useful to understand how a view is created. The CREATE VIEW statement defines a named query that MySQL can use whenever the view is referenced.
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;Suppose a company has an employees table containing employee IDs, names, departments, salaries, cities, and employment statuses.
CREATE VIEW employee_directory AS
SELECT employee_id, employee_name, department
FROM employees;Retrieve the view's data:
SELECT *
FROM employee_directory;The view exposes only the employee ID, name, and department. Its query definition is stored by MySQL, while the data is retrieved from the underlying table when the view is queried.
The following examples use an employee table. If you already have a practice database with equivalent data, you can use it instead.
CREATE DATABASE IF NOT EXISTS business_db;
USE business_db;CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2),
city VARCHAR(50),
employment_status VARCHAR(20)
);INSERT INTO employees
(employee_id, employee_name, department, salary, city, employment_status)
VALUES
(1, 'Aarav Sharma', 'IT', 65000, 'Mumbai', 'Active'),
(2, 'Priya Verma', 'HR', 48000, 'Pune', 'Active'),
(3, 'Rahul Mehta', 'Finance', 58000, 'Mumbai', 'Active'),
(4, 'Sneha Patil', 'IT', 72000, 'Navi Mumbai', 'Active'),
(5, 'Karan Singh', 'Marketing', 45000, 'Pune', 'Inactive'),
(6, 'Neha Joshi', 'Finance', 62000, 'Mumbai', 'Active');SELECT *
FROM employees;These records will help demonstrate how view definitions can be modified as business requirements change.
The ALTER VIEW statement changes the definition of an existing view. It is useful when the view already exists and you want to change its underlying SELECT query or other supported view options.
ALTER VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;Suppose the existing view is:
CREATE VIEW employee_directory AS
SELECT employee_id, employee_name, department
FROM employees;Now the reporting team also needs the city column. Update the view definition:
ALTER VIEW employee_directory AS
SELECT employee_id, employee_name, department, city
FROM employees;Verify the new output:
SELECT *
FROM employee_directory;The view now exposes the city column in addition to the previous columns.
The CREATE OR REPLACE VIEW statement is used to create a view if it does not exist or replace the definition of an existing view if it does.
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;Assume the existing view contains employee IDs and names:
CREATE VIEW employee_summary AS
SELECT employee_id, employee_name
FROM employees;Replace the definition to include department and city:
CREATE OR REPLACE VIEW employee_summary AS
SELECT employee_id, employee_name, department, city
FROM employees;Verify the updated definition's output:
SELECT *
FROM employee_summary;CREATE OR REPLACE VIEW active_employee_directory AS
SELECT employee_id, employee_name, department
FROM employees
WHERE employment_status = 'Active';If active_employee_directory does not exist, MySQL creates it. If it already exists, MySQL replaces its definition with the query shown above.
Both statements can update a view definition, but they serve slightly different purposes.
| Feature | ALTER VIEW | CREATE OR REPLACE VIEW |
|---|---|---|
| Main purpose | Change an existing view definition. | Create a new view or replace an existing view definition. |
| View must already exist | Yes. | No. |
| If the view does not exist | Fails because the view cannot be found. | Creates the view if the source query and permissions are valid. |
| If the view exists | Changes its definition. | Replaces its definition. |
| Common use case | Managing a known existing view. | Deployment scripts that should create or update a view. |
| Required permissions | Requires the applicable view-alteration privileges. | Requires creation privileges and, when replacing, the applicable DROP privilege. |
| Underlying table data | Not directly changed by changing the definition. | Not directly changed by replacing the definition. |
Remember: Use ALTER VIEW when the view is expected to exist. Use CREATE OR REPLACE VIEW when the script should work whether the view is already present or not.
A common reason to manage a view is to add a column required by a report or application.
CREATE OR REPLACE VIEW employee_directory AS
SELECT employee_id, employee_name, department
FROM employees;ALTER VIEW employee_directory AS
SELECT employee_id, employee_name, department, city
FROM employees;CREATE OR REPLACE VIEW employee_directory AS
SELECT employee_id, employee_name, department, city, salary
FROM employees;SELECT *
FROM employee_directory;Important: Adding sensitive columns such as salary may change who should be permitted to access the view. Review privileges before exposing additional information.
To remove a column from a view, rewrite the view definition without that column. MySQL does not provide a separate command that removes only one column from a view without redefining its query.
Suppose the current view includes employee ID, name, department, city, and salary.
CREATE OR REPLACE VIEW employee_directory AS
SELECT employee_id, employee_name, department, city, salary
FROM employees;If salary should no longer be exposed, replace the definition:
ALTER VIEW employee_directory AS
SELECT employee_id, employee_name, department, city
FROM employees;Verify the result:
SELECT *
FROM employee_directory;The salary column is no longer part of this view's output. This does not delete the salary data from the underlying table.
Views often include a WHERE clause to limit which rows are displayed. You can modify this condition when reporting requirements change.
CREATE OR REPLACE VIEW employee_status_view AS
SELECT employee_id, employee_name, department, employment_status
FROM employees
WHERE employment_status = 'Active';ALTER VIEW employee_status_view AS
SELECT employee_id, employee_name, department, employment_status
FROM employees
WHERE department = 'IT';The view now returns IT employees, regardless of their employment status.
CREATE OR REPLACE VIEW active_it_employees AS
SELECT employee_id, employee_name, department, city
FROM employees
WHERE department = 'IT'
AND employment_status = 'Active';This view returns employees who meet both conditions.
CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary >= 60000;The view returns employees whose recorded salary is at least 60,000.
Aliases allow a view to expose column names that are more suitable for reports or applications. Changing an alias can improve clarity, but it may affect queries that rely on the old view column name.
CREATE OR REPLACE VIEW employee_report AS
SELECT
employee_id,
employee_name,
salary
FROM employees;ALTER VIEW employee_report AS
SELECT
employee_id AS id,
employee_name AS full_name,
salary AS monthly_salary
FROM employees;SELECT id, full_name, monthly_salary
FROM employee_report;The view now exposes the aliases id, full_name, and monthly_salary.
Best practice: Before changing a view's output column names, identify applications, reports, and other views that may depend on the existing names.
A view can include expressions and calculations. When a formula changes, the view definition must be updated so that the calculated output reflects the new requirement.
CREATE OR REPLACE VIEW employee_annual_salary AS
SELECT
employee_id,
employee_name,
salary AS monthly_salary,
salary * 12 AS estimated_annual_salary
FROM employees;This example assumes that the stored salary is a monthly amount.
Suppose the business wants to display an estimated annual salary plus a 10% bonus.
ALTER VIEW employee_annual_salary AS
SELECT
employee_id,
employee_name,
salary AS monthly_salary,
salary * 12 AS estimated_annual_salary,
salary * 12 * 0.10 AS estimated_annual_bonus
FROM employees;SELECT *
FROM employee_annual_salary;The annual salary and bonus are calculated when the view is queried. The calculated values are not automatically stored as new columns in the source table.
Views can summarize data using aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX(). When reporting requirements change, you can update the view definition to include different summary values.
CREATE OR REPLACE VIEW department_summary AS
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;ALTER VIEW department_summary AS
SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MAX(salary) AS highest_salary
FROM employees
GROUP BY department;SELECT *
FROM department_summary
ORDER BY total_salary DESC;This view is designed for reporting. Since it groups multiple employees into one row per department, it is generally not directly updatable.
Views can combine information from multiple tables. If a report needs a new field from a related table, the view definition can be modified to include an appropriate join and additional columns.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL,
manager_name VARCHAR(100)
);INSERT INTO departments
(department_id, department_name, manager_name)
VALUES
(1, 'IT', 'Anil Kapoor'),
(2, 'HR', 'Meera Shah'),
(3, 'Finance', 'Rohan Desai'),
(4, 'Marketing', 'Kavita Rao');CREATE OR REPLACE VIEW employee_department_details AS
SELECT
e.employee_id,
e.employee_name,
e.department,
d.manager_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department = d.department_name;ALTER VIEW employee_department_details AS
SELECT
e.employee_id,
e.employee_name,
e.department,
e.city,
e.salary,
d.manager_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department = d.department_name;Verify the result:
SELECT *
FROM employee_department_details;The LEFT JOIN keeps employee rows even when no matching department record exists. In that situation, the manager value will be NULL.
The WITH CHECK OPTION clause can restrict inserts and updates performed through a suitable updatable view so that the resulting rows continue to satisfy the view's filter condition.
CREATE OR REPLACE VIEW active_employee_view AS
SELECT employee_id, employee_name, department, employment_status
FROM employees
WHERE employment_status = 'Active'
WITH CHECK OPTION;CREATE OR REPLACE VIEW active_employee_view AS
SELECT employee_id, employee_name, department, city, employment_status
FROM employees
WHERE employment_status = 'Active'
WITH CHECK OPTION;The updated definition adds the city column while retaining the active-status filter and check option.
Remember that a check option does not make a non-updatable view updatable. It also does not replace table constraints or database access controls.
MySQL views support options that determine whose privileges are used when the view is accessed. When altering or replacing a view, you should understand how these settings affect data access.
With SQL SECURITY DEFINER, access to the underlying objects is generally checked using the privileges of the view's definer account.
CREATE OR REPLACE
SQL SECURITY DEFINER VIEW employee_public_view AS
SELECT employee_id, employee_name, department
FROM employees;With SQL SECURITY INVOKER, access to the underlying objects is checked using the privileges of the user invoking the view.
CREATE OR REPLACE
SQL SECURITY INVOKER VIEW employee_public_view AS
SELECT employee_id, employee_name, department
FROM employees;Changing a view definition can affect access to data even when no underlying table records have changed.
MySQL views support an ALGORITHM option that influences how the view is processed. The common options are UNDEFINED, MERGE, and TEMPTABLE.
| Algorithm | Description |
|---|---|
UNDEFINED |
Allows MySQL to choose an appropriate algorithm. |
MERGE |
Combines the view definition with the referencing query where supported. |
TEMPTABLE |
Processes the view through an internal temporary result table. |
CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW employee_directory AS
SELECT employee_id, employee_name, department
FROM employees;In most learning and application scenarios, allowing MySQL to choose the algorithm is a reasonable default. Do not change the algorithm without understanding its impact on query behavior and view updatability.
After altering or replacing a view, verify the new definition and output. This helps detect mistakes before applications or reports depend on the changed view.
SHOW CREATE VIEW employee_directory;This statement displays the stored definition and relevant view options.
SHOW FULL TABLES
WHERE Table_type = 'VIEW';SELECT
TABLE_SCHEMA,
TABLE_NAME,
IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'business_db';The IS_UPDATABLE value indicates whether MySQL considers the view updatable according to its definition and applicable rules.
SELECT *
FROM employee_directory
LIMIT 10;Check the returned column names, row values, and filtering behavior against the expected results.
| Error or Problem | Possible Cause | Solution |
|---|---|---|
| View does not exist | ALTER VIEW was used with an incorrect name or missing view. |
Verify the name and database, or use CREATE OR REPLACE VIEW when appropriate. |
| View already exists | CREATE VIEW was used without replacing an existing view. |
Use ALTER VIEW or CREATE OR REPLACE VIEW. |
| Unknown column | The query references a column that does not exist. | Inspect the table structure and correct the column name. |
| Access denied | The account lacks the required view or source-column privileges. | Ask the database administrator to review the permissions. |
| View becomes invalid | An underlying table or referenced column was renamed or removed. | Inspect the definition and update it to match the current schema. |
| Application query fails | The view's output columns or aliases changed. | Update dependent queries or preserve the expected output structure. |
| Check option rejects a write | An insert or update violates the view's filter. | Use values that satisfy the condition or review the business rule. |
| Unexpected output | The replacement query uses a different join, filter, or calculation. | Run the underlying SELECT independently and compare the results. |
SELECT DATABASE();.SELECT query independently.SHOW CREATE VIEW.Although these statements are useful for managing view definitions, they have important limitations.
SELECT * for stable application interfaces.ALTER VIEW when the view must already exist.CREATE OR REPLACE VIEW for repeatable deployment scripts that create or update a view.Scenario: A company has an employee directory view used by HR. Initially, the view contains employee IDs and names. Later, the HR team needs department and city information. Eventually, the company wants a separate view for active employees.
CREATE VIEW hr_employee_directory AS
SELECT employee_id, employee_name
FROM employees;ALTER VIEW hr_employee_directory AS
SELECT employee_id, employee_name, department, city
FROM employees;CREATE OR REPLACE VIEW hr_employee_directory AS
SELECT employee_id, employee_name, department, city
FROM employees
WHERE employment_status = 'Active';The view now returns active employees only.
CREATE OR REPLACE VIEW employee_salary_report AS
SELECT
employee_id,
employee_name,
department,
salary AS monthly_salary,
salary * 12 AS estimated_annual_salary
FROM employees;SELECT *
FROM hr_employee_directory;
SELECT *
FROM employee_salary_report;
SHOW CREATE VIEW hr_employee_directory;
SHOW CREATE VIEW employee_salary_report;Business benefit: The company can manage different views for different reporting needs without duplicating the underlying employee data. Appropriate permissions should be configured to control who can access salary information.
Project objective: Practice creating, altering, replacing, and verifying views in a realistic database workflow.
ALTER VIEW to add department and city.CREATE OR REPLACE VIEW to restrict the directory to active employees.SHOW CREATE VIEW.INFORMATION_SCHEMA.VIEWS.After completing the project, you should be able to maintain view definitions, respond to changing report requirements, and recognize the impact of view changes on dependent applications.
employee_basic_view containing employee ID and name.ALTER VIEW to add the department column.CREATE OR REPLACE VIEW to add the city column.ALTER VIEW is used on a view that does not exist.CREATE OR REPLACE VIEW employee_basic_view AS
SELECT employee_id, employee_name
FROM employees;ALTER VIEW employee_basic_view AS
SELECT employee_id, employee_name, department
FROM employees;CREATE OR REPLACE VIEW employee_basic_view AS
SELECT employee_id, employee_name, department, city
FROM employees;ALTER VIEW employee_basic_view AS
SELECT employee_id, employee_name, department, city
FROM employees
WHERE department = 'IT';CREATE OR REPLACE VIEW employee_salary_report AS
SELECT
employee_id,
employee_name,
salary AS monthly_salary,
salary * 12 AS estimated_annual_salary
FROM employees;ALTER VIEW employee_salary_report AS
SELECT
employee_id,
employee_name,
salary AS monthly_salary,
salary * 12 AS estimated_annual_salary,
salary * 12 * 0.10 AS estimated_annual_bonus
FROM employees;CREATE OR REPLACE VIEW department_summary AS
SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;SHOW CREATE VIEW employee_basic_view;SHOW FULL TABLES
WHERE Table_type = 'VIEW';Answer: ALTER VIEW changes the definition of an existing view.
Answer: It creates a view if it does not exist or replaces the definition of an existing view.
Answer: ALTER VIEW requires an existing view, whereas CREATE OR REPLACE VIEW can create a missing view or replace an existing one.
Answer: No. It changes the view definition rather than directly modifying stored table data.
Answer: Yes. The replacement query can select additional columns from the underlying tables.
Answer: The statement fails because the view must already exist.
Answer: Yes. Redefine the view without the column using ALTER VIEW or CREATE OR REPLACE VIEW.
Answer: Redefine the view with the desired WHERE condition.
Answer: Use SHOW CREATE VIEW view_name;.
Answer: Yes. A view can include expressions and calculated output columns.
Answer: It can. Applications and reports that reference the old names may need to be updated.
Answer: It constrains eligible inserts and updates through an updatable view so that resulting rows satisfy the view's filter condition.
Answer: The account needs the relevant creation and source-column privileges, as well as the applicable DROP privilege when replacing an existing view. Exact requirements depend on the definition and security settings.
Answer: It means privileges on underlying objects are checked using the account invoking the view.
Answer: Testing helps identify incorrect results, broken dependent queries, permission problems, and unexpected performance changes.
Answer: No. A regular view stores a query definition rather than a separate copy of its result set.
Answer: Yes. A view can combine information from multiple tables using joins.
Answer: It lists views in the selected database.
Answer: Explicit column lists make the view's output structure more predictable and reduce confusion when the underlying table schema changes.
Answer: Inspect the existing definition, test the new query, review dependencies and permissions, apply the change, and verify the output and dependent applications.
ALTER VIEW.CREATE OR REPLACE VIEW.SHOW CREATE VIEW.SHOW FULL TABLES.INFORMATION_SCHEMA.VIEWS.Managing views using ALTER VIEW and CREATE OR REPLACE VIEW is an essential MySQL skill. These statements allow database developers to maintain reusable query definitions as reporting requirements, business rules, and application needs evolve.
ALTER VIEW is suitable for changing an existing view, while CREATE OR REPLACE VIEW can create a new view or replace an existing definition. Both approaches require careful attention to output columns, filtering conditions, permissions, and dependent applications.
By using explicit column lists, testing replacement queries, inspecting view definitions, and reviewing security settings, you can manage views more safely and maintain reliable database applications.
Continue Learning: Explore more database concepts through the MySQL Training Course or register for a course demo.
