DIFFERENCE BETWEEN SQL DELETE, DROP & TRUNCATE

SQL DELETE, DROP & TRUNCATE

SQL Delete Statement

The DELETE Statement is used to delete rows/records from a table. After performing a 

DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change 

permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to 

fire.

SYNTAX:

DELETE FROM table_name WHERE [condition];

• table_name -- the table name which has to be updated.

NOTE:The WHERE clause in the sql delete command is optional and it identifies the rows

 in the column that gets deleted. Notice the WHERE clause in the DELETE syntax. 

The WHERE clause specifies which record or records that should be deleted.

 If you omit the WHERE clause, all records will be deleted.

TABLE: Persons

P_Id	LastName	FirstName	Address	City
1	Hasen	Oklia	Taralabalu 10	Sambalpur
4	Niel	Takia	Vasnath 23	Sitara
3	Parashuram	Kari	Sambar 20	Sitara
2	Svastik	Tove	Bagalpur 23	Sambalpur

For Example: To delete a person’s record with last name ‘Tove’ from the Persons table, 

the sql delete query would be like,

DELETE FROM Persons WHERE Lastname = ‘Tove’;

The first line in the SQL DELETE statement above specifies the table that we are deleting the

 record(s) from table Persons. The second line (the WHERE clause) specifies which rows exactly 

do we delete (in our case all rows which has LastName of ‘Tove’). As you can see the 

DELETE SQL queries have very simple syntax and in fact are very close to the natural language.

But wait, there is something wrong with the statement above! The problem is that we can

 have more than one person having last name of ‘Tove’, and all with this last name will be deleted. 

Because we don’t want to do that, we need to find a table field or combination of fields 

that uniquely identifies the Person ‘Svastik Tove’.

Looking at the Persons table an obvious candidate for such a unique field is the P_ID column.

 Our improved SQL query which deletes only the record of Svastik Tove’’s record will look like 

this:

DELETE FROM Persons
WHERE P_ID = 2;

To delete all the rows from the table, the query would be like,

DELETE FROM employee;

SQL TRUNCATE Statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the

 space containing the table.

SYNTAX:

TRUNCATE TABLE table_name;

For Example: To delete all the rows from employee table, the query would be like,

TRUNCATE TABLE Persons;

Difference between DELETE,DROP and TRUNCATE Statements:

DELETE Statement: This command deletes only the rows from the table based on the 

condition given in the where clause or deletes all the rows from the table if no condition is

 specified. But it does not free the space containing the table. It generates REDO information.

TRUNCATE statement: This command is used to delete all the rows from the table

 and free the space containing the table. The operation cannot be rolled back and 

no triggers will be fired, TRUCATE is faster and doesn't use as much undo space as a DELETE.

TRUNCATE command resets the High Water Mark for the table

SQL DROP Statement:

The SQL DROP command is used to remove an object from the database.

 If you drop a table, all the rows in the table is deleted and the table structure is

 removed from the database. Once a table is dropped we cannot get it back, so be careful 

while using RENAME command. When a table is dropped all the references 

(rows, indexes and privileges) to the table will not be valid. Drop command removes the

 table from data dictionary.

SYNTAX:

DROP TABLE table_name;

For Example: To drop the table employee, the query would be like

DROP TABLE employee;

Difference between DROP and TRUNCATE Statement:

If a table is dropped, all the relationships with other tables will no longer be valid, 

the integrity constraints will be dropped, grant or access privileges on the table will also

 be dropped, if want use the table again it has to be recreated with the integrity 

constraints, access privileges and the relationships with other tables should be

 established again. But, if a table is truncated, the table structure remains the same; 

therefore any of the above problems will not exist.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. 

Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE

 operations cannot be rolled back.