Open In App

Difference between DELETE and DROP in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

Delete and Drop commands both are used to remove the data from the database but the Delete command is used to remove some or all the tuples from a relation and the Drop command is used to remove the named elements of the schema like relations, domains and it also removes entire data from the database. Let’s start with DELETE Command first.

What is DELETE Command in SQL?

DELETE is a Data Manipulation Language (DML) command and is used when you want to remove some or all the tuples from a relation. If the WHERE clause is used along with the DELETE command it removes only those tuples which satisfy the WHERE clause condition but if the WHERE clause is missing from the DELETE statement then by default all the tuples present in relation are removed.

Syntax

DELETE FROM relation_name

WHERE condition;

Query

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);

Output

Customer Table

Customer Table

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

 DELETE FROM Customer where FIRST_NAME='Mohit';



Output

Table 1

Table 1

What is DROP Command in SQL?

DROP is a Data Definition Language (DDL) command which removes the named elements of the schema like relations, domains, or constraints and you can also remove an entire schema using the DROP command.

Syntax

DROP SCHEMA schema_name RESTRICT;

DROP Table table_name CASCADE;

Example:

DROP TABLE Customer;

DROP DATABASE

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

Syntax

DROP DATABASE NAME_OF_DATABASE;

DROP COLUMN

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

Syntax

ALTER TABLE table_nameDrop COLUMN column_name;

DROP INDEX

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

Syntax

DROP INDEX IF EXISTS index_name

ON table_name;

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

Last Updated : 23 Aug, 2023
Like Article
Save Article
Share your thoughts in the comments
Similar Reads