Difference between DETETE, DROP and TRUNCATE Commands in SQL

Difference between DETETE, DROP and TRUNCATE Commands in SQL

I had some difficulty working with these 3 commands back when I was learning SQL particularly when deciding which one to use. SO, I've dedicated this blog to differentiating the three of them.

DELETE: used to remove rows selectively from a table. Delete is a DML command, so it will cause any delete triggers on the table to fire. You may need to explicitly commit after deleting from a table to make changes permanent.

Example:

SELECT COUNT(*) FROM employees;
COUNT(*)
--------
20

DELETE FROM employees WHERE ename = 'John';
5 rows deleted.

COMMIT;
Commit complete.

SELECT COUNT(*) FROM employees;
COUNT(*)
--------
15

Quick tip: you can omit the WHERE clause to delete all rows from the table. Also, delete command can be rolled back.

DROP: used to drop a table from the database. No triggers will fire as Drop is a DDL command. The table's data, indexes and any priviledges are removed. Drop command cannot be rolled back.

Example:

DROP TABLE employees;
Table dropped.

SELECT COUNT(*) FROM employees;
--will give an error that the table does not exist, try for yourself!!

Quick tip: Starting oracle 10g, you can undrop a table:

FLASHBACK TABLE employees TO BEFORE DROP:
Flashback complete.


TRUNCATE: used to remove all rows from a table. No triggers will fire as Truncate is also a DDL command. It can't be rolled back too. This command is fastest among the three as it takes relatively lesser undo space.

Example:

TRUNCATE TABLE employees;
Table truncated.

SELECT COUNT(*) FROM employees;
COUNT(*)
--------
0

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com

Hyperion Planning Performance Tunning Automate
Oracle Fusion Applications Login Problem

Related Posts