How to Drop a View in MySQL: Syntax and Examples

To drop a view in MySQL, use the DROP VIEW view_name; statement. This command removes the view from the database permanently.

Syntax

The basic syntax to drop a view in MySQL is:

• DROP VIEW view_name; - removes the specified view.
• IF EXISTS - optional clause to avoid error if the view does not exist.

sql

DROP VIEW [IF EXISTS] view_name;

Example

This example shows how to drop a view named employee_view. It first creates the view, then drops it.

sql

CREATE VIEW employee_view AS SELECT id, name FROM employees;

DROP VIEW employee_view;

Output

Query OK, 0 rows affected (for both CREATE VIEW and DROP VIEW)

Common Pitfalls

Common mistakes when dropping views include:

• Trying to drop a view that does not exist without IF EXISTS, which causes an error.
• Confusing views with tables; DROP VIEW only works on views.

sql

DROP VIEW employee_view;
-- If the view does not exist, this causes an error.

DROP VIEW IF EXISTS employee_view;
-- This safely drops the view only if it exists.

Quick Reference

Command	Description
DROP VIEW view_name;	Drops the specified view.
DROP VIEW IF EXISTS view_name;	Drops the view only if it exists, avoiding errors.
SHOW FULL TABLES WHERE Table_type = 'VIEW';	Lists all views in the current database.

Key Takeaways

Use DROP VIEW view_name; to remove a view in MySQL.

Add IF EXISTS to avoid errors if the view does not exist.

DROP VIEW only works on views, not tables.

Check existing views with SHOW FULL TABLES WHERE Table_type = 'VIEW';