In this blog am going to talk about some confusing and most frequent asked commands in SQL, these are near
most similar but functionality is different in each case. Here we go:
DELETE Command
Delete Command is used for deleting or eliminating a particular entry or a row from the table using
'WHERE' clause. If you will give a condition through a WHERE clause then it will remove few rows from
your table otherwise it will remove all the rows.
After applying DELETE clause on your table, you can also apply COMMIT or ROLLBACK operation in order
to undo your changes in the particular table.
Example
DELETE from EMP_TABLE
where (AGE=21);
Keys
- Commit and rollback are possible
- Requires more space
- Triggers can be fired
- It is a DML command
TRUNCATE Command
Truncate directly removes all the rows available in the table. It does not require any WHERE clause in its statement while applying changes on the table. No COMMIT or ROLLBACK operation will possible after applying TRUNCATE command.
This command is faster than the delete command as, it does not take much memory space while performing certain operation.
Example
TRUNCATE EMP_TABLE;
Keys
- Commit and rollback are not possible
- Requires very less space
- No need to fire Triggers
- Faster execution
- It is a DDL command
DROP Command
Drop command is used for eliminating the whole table from your database. No COMMIT or ROLLBACK
operation will possible after applying TRUNCATE command.
Example
DROP EMP_TABLE:
Keys
- Commit and rollback are not possible
- Requires no space
- No need to fire Triggers
- More fatser