Difference Between DELETE and DROP in SQL

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

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

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

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.