SQL DROP TABLE Statement
The
DELETE FROM statement is used to delete existing records in a table, whereas DROP TABLE statement not only deletes the whole table, but also drops the table including the table structure, attributes, and indexes.
Be careful before dropping a table.
The SQL statement drops the existing table Shippers:
|
|
SQL TRUNCATE TABLE Statement
The
TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
The SQL command is similar to:
DELETE FROM Shippers; |
|
☠ Caution ☠: You cannot roll back a
TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
DELETE FROM vs TRUNCATE TABLE
DELETE FROM and TRUNCATE TABLE are the commands use to remove tuples from a relation, but they differ in many contexts.
In SQL, DELETE FROM command is a DML (Data Manipulation Language) command, whereas TRUNCATE TABLE command is a DDL (Data Definition Language) command.
However, the point that allows us to differentiate between DELETE FROM and TRUNCATE TABLE is that DELETE FROM is able to remove specified tuples from a relation, whereas the TRUNCATE TABLE command removes entire tuples from a relation.
For the detailed differences, check this.
Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.
|
Result:
|
The Database includes:
|
|
The Database includes:
|