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