How to Drop a View in MySQL

08/06/2021

Contents

In this article, you will learn how to drop a view in MySQL.

Dropping a view in MySQL

In MySQL, a view is a virtual table that represents the result set of a SELECT query. Sometimes, we may want to remove a view from the database due to various reasons such as a change in business requirements or to free up database space.

Syntax

The syntax to drop a view in MySQL is as follows:

DROP VIEW [IF EXISTS] view_name;

Here, IF EXISTS is an optional clause that checks whether the view exists before dropping it. If the view exists, then it is dropped, else no action is taken. The view_name is the name of the view that needs to be dropped.

Examples

Let’s say we have created a view named “customer_orders” using the following SQL query:

CREATE VIEW customer_orders AS
SELECT customer_name, order_id, order_date, amount
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

To drop this view, we can use the following SQL query:

DROP VIEW IF EXISTS customer_orders;

Here, the IF EXISTS clause checks whether the view named “customer_orders” exists. If it exists, then it will be dropped.

Note: Dropping a view does not affect the original table(s) used to create the view. If you drop a view that is based on one or more tables, the underlying tables are not affected.

#MySQL