Difference Between DELETE and DROP in SQL

Last Updated : 29 Nov, 2024

In SQL, the DELETE and DROP commands are essential for managing data in a database, but they serve different purposes. While both are used to remove data, their functionality varies significantly. The DELETE command is designed to remove specific rows (tuples) or all rows from a table while preserving the table structure. In contrast, the DROP command is used to permanently delete named elements of the schema. Understanding these commands is crucial for effective database management.

This article provides a detailed comparison of these commands, their syntax, examples, and the scenarios where each is best suited. By the end of this article, we will clearly understand how to use DELETE and DROP commands efficiently.

Comparison Between DELETE and DROP Command

Parameter	DELETE	DROP
Basic	It removes some or all the tuples from a table.	It removes the entire schema, table, domain, or constraints from the database.
Language	Data Manipulation Language command	Data Definition Language command.
Clause	WHERE clause is mainly used along with the DELETE command.	No clause is required along with the DROP command.
Rollback	Actions performed by DELETE can be rolled back as it uses a buffer.	Actions performed by DROP can’t be rolled back because it directly works on actual data.
Space	space occupied by the table in the memory is not freed even if you delete all the tuples of the table using DELETE	It frees the table space from memory
Main Issue	Shortage of memory	Memory fragmentation
Locality of reference	Excellent	Adequate
Flexibility	Fixed-size	Resizing is possible

What is DELETE Command in SQL?

The DELETE command is part of SQL’s Data Manipulation Language (DML). It is used to remove specific rows or all rows from a table without deleting the table’s structure. The WHERE clause is often used with DELETE to specify the rows to be removed. If the WHERE clause is skipped, all rows in the table are deleted.

Key Features of DELETE Command

Selective Deletion: Deletes specific rows based on a condition.
Retention of Structure: The table structure, indexes, and constraints remain intact.
Rollback Support: DELETE operations can be rolled back if used within a transaction.

Syntax

DELETE FROM relation_name WHERE condition;

Example: Using the DELETE Command

Step 1: Create a Table and Insert Data

Here, we create a Customer table with CUSTOMERID, FIRST_NAME, LAST_NAME, and AGE.

CREATE TABLE Customer(   CUSTOMERID INT PRIMARY KEY,   FIRST_NAME VARCHAR(50),   LAST_NAME VARCHAR(50),   AGE INT)  INSERT INTO Customer(CUSTOMERID,FIRST_NAME,LAST_NAME,AGE) VALUES (1,'Mohit','Kumar',21), (2,'Praful','Singh',22), (3,'Ritik','Kumar',25), (4,'Vishnu','Yadav',26);

Step 2: View the Data

SELECT * FROM Customer;

Output

Customer Table

Step 3: Delete a Specific Row

If you want to remove some tuples from the Customer Table then we use the DELETE command.

 DELETE FROM Customer where FIRST_NAME='Mohit';

Step 4: View the Updated Data

SELECT * FROM Customer;

Output

Table 1

What is DROP Command in SQL?

The DROP command belongs to SQL’s Data Definition Language (DDL). It is used to completely remove database objects such as tables, schemas, or indexes. When a table or object is dropped, it is permanently deleted, including its structure, indexes, and constraints.

Key Features of DROP Command

Permanent Deletion: Removes the table or object completely from the database.
Frees Memory: Clears the memory space occupied by the table.
Irreversible: Once a table is dropped, it cannot be recovered unless a backup exists.

Syntax

Drop a Table

DROP TABLE table_name;

Drop a Schema

DROP SCHEMA schema_name RESTRICT;

Example: Using DROP Command

Step 1: Drop Customer Table

This command permanently removes the table along with its data, structure, indexes, and constraints.

DROP TABLE Customer;

Step 2: Drop Database

Drop Database is used to remove the existing SQL Database from the Server.

DROP DATABASE  NAME_OF_DATABASE;

Step 3: DROP COLUMN

The Drop Column is used to remove the existing SQL column from the database.

ALTER TABLE table_nameDrop COLUMN column_name;

Step 4: DROP INDEX

Drop Index is used to remove the index in a table.

DROP INDEX IF EXISTS index_name ON table_name;

Conclusion

The DELETE and DROP commands are both crucial for managing data and database objects in SQL, but they serve different purposes. Use DELETE when we need to remove specific rows or all rows from a table while keeping its structure intact. Choose DROP when we need to permanently delete a table or database object.

Understanding these commands ensures efficient database management and helps maintain data integrity while optimizing storage space. Always back up our data before using the DROP command to avoid unintentional data loss.

Difference between DELETE and TRUNCATE

Ankit_Bisht

Improve

Article Tags :