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