MySQL DROP VIEW

By /
Drop View Statement

In this tutorial, we will learn about the DROP VIEW statement to delete one or more views from the database. We will also see how to accomplish error handling if the view that you are trying to delete does not exist. So, let’s get started!

Also read: MySQL Rename View

Introduction to MySQL DROP VIEW Statement

The DROP VIEW statement is used to delete the view from the database completely.

Note that, you must possess the DROP privilege for each view to remove them from the database.

The syntax to delete the drop from the MySQL database is-

DROP VIEW view_name;Code language: SQL (Structured Query Language) (sql)

The above syntax can be used to delete the view that exists in the database. However, if the view that you are trying to delete doesn’t exist, the query will throw an error.

To prevent the query from an error, MySQL offers you the IF EXISTS Optional parameter.

DROP VIEW [IF EXISTS] view_name;
Code language: SQL (Structured Query Language) (sql)

Here, if you try to delete a view that doesn’t exist, MySQL will show you a warning instead of throwing an error.

You can also delete multiple views using a single query using the following syntax-

DROP VIEW view_name1 [,view_name2]...;
Code language: SQL (Structured Query Language) (sql)

Note that, if you try to delete multiple views in a single query and one or more views from the list you specified don’t exist, none of the views will be deleted even if they exist.

For example, in the above syntax, if the view_name1 exists and view_name2 doesn’t exist then the query will simply throw an error and view_name1 won’t be removed even if it exists.

To prevent the error, you can use the IF EXISTS option.

DROP VIEW [IF EXISTS] view_name1 [,view_name2]...;
Code language: SQL (Structured Query Language) (sql)

If you use the above syntax and if one or more views don’t exist, then you will get a warning saying that “unknown table viewXYZ”. And, all the existing views in the list specified by you will get deleted.

Examples of MySQL DROP VIEW Statement

Here, we have created three views in our database already. We will perform operations on these views only.

You can get the list of views using the following command-

SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';Code language: SQL (Structured Query Language) (sql)
Show Views List
Show Views List

As you can see, we have three different views in our database. Now, we will start with our examples.

Drop Non-Existing View

Here, we will try to delete a view that doesn’t exist.

DROP VIEW random_view;
DROP VIEW IF EXISTS random_view;Code language: SQL (Structured Query Language) (sql)

Here, if you execute the first query, you will get an error saying that “unknown table table_name”.

However, if you try to delete the view using the IF EXISTS clause, the query will get executed successfully with a warning.

Drop View If Exists
Drop View If Exists

You will get an output as shown in the highlighted area.

Drop Existing View

Now, we will delete the existing view using a simple DROP VIEW statement. Even if you are sure that view does exist in your database, you should use the IF EXISTS option as it is a good practice.

DROP VIEW IF EXISTS cityList;Code language: SQL (Structured Query Language) (sql)
Drop Existing View
Drop Existing View

As you can see, the view is deleted from the database.

Drop Multiple Views

Now we are left with two views in our database. We will perform operations on these two views only.

Here, we will specify the names of three views to be deleted out of which one doesn’t exist in our database.

We will not specify the IF EXISTS option to see if the existing views get deleted or not.

DROP VIEW fetchData, empList, any_view;Code language: SQL (Structured Query Language) (sql)
Drop Multiple Views
Drop Multiple Views

As you can see, even if the fetchData and empList views are present in the database, they didn’t get deleted. Also, we have received an error.

Now, let’s try deleting multiple views using the IF EXISTS option.

DROP VIEW IF EXISTS fetchData, empList, any_view;Code language: SQL (Structured Query Language) (sql)

When you execute the above query, it will delete all the existing views and show you a warning for the non-existing views.

Drop Multiple Views Using IF EXISTS
Drop Multiple Views Using IF EXISTS

As you can see in the output above, the first query was executed successfully with a warning and deleted the existing views. If we display the warning, it will show the “unknown table …”. Finally, if we try to display the view list, we will see nothing because everything is deleted.

Summary

In this tutorial, we have learned –

  • What is the DROP VIEW statement
  • Syntax of the DROP VIEW Statement
  • How to use the IF EXISTS option
  • How to delete a single view
  • How to delete multiple views

References

MySQL Official documentation on the DROP VIEW Statement.