SQL - DROP View

Last Updated : 08 Jan, 2025

SQL Views provide a powerful way to simplify complex queries and present data in a more understandable format. However, there may be times when we need to remove a view from our database schema. In SQL, deleting a view is straightforward using the DROP VIEW command.

In this article, we will explain the process of deleting views in SQL, why and when to remove a view, and provide examples and outputs to demonstrate how to effectively manage views in our database.

SQL DROP VIEW Command

In SQL, there is no direct DELETE VIEW command. To remove a view from a database, you must use the DROP VIEW command. This command deletes the view definition from the database. However, DROP VIEW does not affect any underlying data in the base tables that the view was created from. It only removes the virtual table (view) from the database schema.

Syntax:

DROP VIEW view_name;

Creating Views

Consider the following EMPLOYEES table that stores data about employees. This table contains important information such as employee names, positions, salaries, and departments. We can create views to simplify the querying process and present data in a more structured way, focusing on specific subsets of the data.

1. View for High Salary Employees

The HighSalaryEmployees view filters the EMPLOYEES table to include only those employees with a salary greater than 50,000. The query retrieves columns such as ID, Name, Position, Salary, and Department, and returns records for employees who meet the salary condition.

Query:

CREATE VIEW HighSalaryEmployees AS
SELECT * FROM EMPLOYEES WHERE Salary > 50000;

Output

ID	Name	Position	Salary	Department
1	Rohan	Manager	57000	HR
3	Arpit	Senior Developer	60000	IT

2. View for Developers

The Developers view filters the EMPLOYEES table to retrieve employees whose Position contains the word "Developer". The view returns the Name, Position, and Department of employees who have roles related to development within the IT department.

Query:

CREATE VIEW Developers AS
SELECT Name, Position, Department FROM EMPLOYEES WHERE Position LIKE '%Developer%';

Output

Name	Position	Department
Aryan	Developer	IT
Arpit	Senior Developer	IT
Tara	Developer	IT

3. View for Employees in IT Department

The ITEmployees view filters the EMPLOYEES table to include only those employees who belong to the IT department. It retrieves all columns (ID, Name, Position, Salary, and Department) for employees working in the IT department, making it easier to query and manage IT-related employee data.

Query:

CREATE VIEW ITEmployees AS
SELECT * FROM EMPLOYEES WHERE Department = 'IT';

Output

ID	Name	Position	Salary	Department
2	Aryan	Developer	45000	IT
3	Arpit	Senior Developer	60000	IT
5	Tara	Developer	55000	IT

To check the Created Views

SELECT TABLE_SCHEMA, 
EMPLOYEES FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='GFG';

To confirm that our views are created we can use the query mentioned above it will show us so our the views that we have created in form of table something like this:

TABLE_SCHEMA	EMPLOYEES
GFG	HighSalaryEmployees
GFG	GOOD_EMPLOYEE
GFG	TOP_EMPLOYEE

Deleting a View

Once we no longer need a view, we can delete it using the DROP VIEW command. For example, let’s delete the HighSalaryEmployees and ITEmployees views.

DROP VIEW HighSalaryEmployees;
DROP VIEW TOP_EMPLOYEE;

After executing this, these views will no longer exist in the database schema. We can confirm by querying the INFORMATION_SCHEMA.VIEWS table to see the remaining views:

SELECT TABLE_SCHEMA, EMPLOYEES 
FROM 
INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='GFG';

Output:

TABLE_SCHEMA	EMPLOYEES
GFG	GOOD_EMPLOYEE

Explanation:

This query retrieves information about views from the INFORMATION_SCHEMA.VIEWS table for a specific TABLE_SCHEMA ('GFG'). It selects the TABLE_SCHEMA and EMPLOYEES columns from the VIEWS table, filtering the results to only include views from the 'GFG' schema.

Verifying the Deletion

To confirm the views have been successfully deleted, we can run the following query again. The HighSalaryEmployees and ITEmployees views should no longer appear in the result.

Query:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_SCHEMA = 'your_schema_name';

Explanation:

The above query retrieves all the views in the specified schema. After deleting the views, only the Developers view will remain.

Why and When Should You Use DROP VIEW?

Outdated Views: If a view is no longer relevant because the underlying data structure has changed or the view is based on obsolete requirements, it’s a good idea to drop it to keep the database clean.
Performance Optimization: Removing unnecessary views can help optimize query performance and maintain database health.
Security: Views may sometimes expose sensitive data or provide too much access. Dropping a view when it's no longer needed helps maintain the principle of least privilege.
Database Maintenance: As our database grows, managing views becomes critical. Keeping only the necessary views can improve overall maintainability.

Conclusion

The DROP VIEW command in SQL is a simple and effective way to remove views that are no longer needed in the database schema. It is essential for maintaining a clean and efficient database by eliminating outdated or unused views. Understanding when to use DROP VIEW helps optimize database performance, reduce clutter, and enhance security. By mastering the DROP VIEW command, we ensure that our database remains organized and easy to manage, ensuring only relevant views are present and that we comply with data access control best practices.

SQL - DROP View

writer01

Improve

Article Tags :

SQL - DROP View

SQL DROP VIEW Command

Creating Views

1. View for High Salary Employees

2. View for Developers

Output

3. View for Employees in IT Department

To check the Created Views

Deleting a View

Verifying the Deletion

Why and When Should You Use DROP VIEW?

Conclusion

Similar Reads