Difference between DELETE and DROP in SQL
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
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
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 |
Contact Us