DELETE VS DROP in SQL
This page contains all the information about the Delete command and Drop command concept and the difference between the DELETE and DROP commands in SQL.
What is the DELETE command in SQL?
The DELETE is a Data Manipulation Language command in the SQL, which is used to remove one record or all the records from the table. The WHERE clause is used in the DELETE query to remove specific records from the tables
The syntax of the DELETE command is:
DELETE FROM Table_Name;
The above syntax is used to remove all the records from the tuples.
The syntax of the DELETE command to remove specific records is as follows:
DELETE FROM Table_Name WHERE Expression;
Example of the DELETE command
Consider the already existing table name Diploma_Student:
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 1 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Mahesh Kumbhar | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Anuja Wanare | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 5 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 4 |
202225 | Akshay Agarwal | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 5 |
Example 1: Write a query to remove records from Diploma_Student where the Student_Id are 202224 and 202225.
DELETE FROM Diploma_Student WHERE Student_Id IN (202224, 202225);
The above query deletes student details from the Diploma_Student where the Student_Id is 202224 and 202225. We will run the select query to check whether the student’s detail is removed or not from the table.
SELECT * FROM Diploma_Student;
The output of the above query is as follows:
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 1 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Mahesh Kumbhar | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Anuja Wanare | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 5 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
Example 2: Write a query to remove all the data from the table.
DELETE FROM Diploma_Student;
The above query deletes all the data from the Diploma_Student table.
The output of the above query will be the empty set. As we deleted all the records, the table is empty.
What is DROP Command in SQL?
The DROP is a Data Definition Language used to remove the elements schemas, like table schema, constraints, etc. The DROP command is also used to remove the index, view, and database.
The syntax of the DROP command is:
DROP TABLE Table_Name;
The above syntax is used to remove the table from the database.
Example of DROP command
Consider the already existing table named Employees:
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | SALARY | CITY | DEPARTMENT | MANAGERID |
1001 | VAIBHAVI | MISHRA | 65500 | PUNE | ORACLE | 1 |
1002 | VAIBHAV | SHARMA | 60000 | BANGALORE | C# | 4 |
1003 | NIKHIL | VANI | 50500 | HYDERABAD | FMW | 2 |
2001 | PRACHI | SHARMA | 55500 | CHANDIGARH | ORACLE | 1 |
2002 | BHAVESH | JAIN | 65500 | PUNE | FMW | 2 |
2003 | RUCHIKA | JAIN | 50000 | MUMBAI | C# | 4 |
3001 | PRANOTI | SHENDE | 55500 | PUNE | JAVA | 3 |
3002 | ANUJA | WANRE | 50500 | HYDERABAD | FMW | 2 |
3003 | DEEPAM | JAUHARI | 58500 | MUMBAI | JAVA | 3 |
4001 | RAJESH | GOUD | 60500 | MUMBAI | TESTING | 5 |
4002 | ASHWINI | BAGHAT | 54500 | BANGALORE | JAVA | 3 |
4003 | RUCHIKA | AGARWAL | 60000 | DELHI | ORACLE | 1 |
5001 | ARCHIT | SHARMA | 55500 | DELHI | TESTING | 5 |
5002 | RAKESH | KUMAR | 70000 | CHANDIGARH | C# | 4 |
5003 | MANISH | SHARMA | 62500 | BANGALORE | TESTING | 5 |
Example: Write a query to drop employees' tables from the database.
DROP TABLE Employees;
The above query drops the employees' table from the database as we run the select query; the table doesn't exist displayed as output.
The output of the above query is as follows:
Now, let's discuss more differences between the DELETE and DROP queries in the query:
Sr.No | DELETE | DROP |
1 | DELETE command is used to delete all the records or the specific records from the table | DROP command is used to drop all the schema, constraints, and tables from the database. |
2 | DELETE is a DML command | DROP is a DDL command |
3 | We can use the WHERE clause in the DELETE query. | No use of the WHERE clause in the DROP query. |
4 | We can roll back the operation performed on the DELETE query | We cannot roll back the operation performed on the DROP query. |
5 | Doesn’t free the memory space | Frees the memory space |
6 | Shortage of Memory | Memory Fragmentation |