DELETE:
The DELETE Statement is used to delete rows from a table.The WHERE clause in the sql delete command is optional and WHERE clause can be used to only remove some rows.If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
For Example: To delete an customer with id 100 from the customer table, the sql delete query would be like,
DELETE FROM customer WHERE id = 100;
To delete all the rows from the customer table, the query would be like,
DELETE FROM customer;
TRUNCATE:
TRUNCATE removes all rows from a table free the space containing the table.. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn’t use as much undo space as a DELETE.
Syntax: TRUNCATE TABLE table_name;
DROP:
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 DROP command. When a table is dropped all the references to the table will not be valid.
Syntax: DROP TABLE table_name;
NOTE: DROP and TRUNCATE are DDL(Data Definition Language) commands, whereas DELETE is a DML(Data Manipulation Language) command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
The DELETE Statement is used to delete rows from a table.The WHERE clause in the sql delete command is optional and WHERE clause can be used to only remove some rows.If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
For Example: To delete an customer with id 100 from the customer table, the sql delete query would be like,
DELETE FROM customer WHERE id = 100;
To delete all the rows from the customer table, the query would be like,
DELETE FROM customer;
TRUNCATE:
TRUNCATE removes all rows from a table free the space containing the table.. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn’t use as much undo space as a DELETE.
Syntax: TRUNCATE TABLE table_name;
DROP:
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 DROP command. When a table is dropped all the references to the table will not be valid.
Syntax: DROP TABLE table_name;
NOTE: DROP and TRUNCATE are DDL(Data Definition Language) commands, whereas DELETE is a DML(Data Manipulation Language) command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.