SQL DROP and TRUNCATE TABLE Statements


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:
 DROP TABLE table_name;

SQL> DROP 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;
 TRUNCATE TABLE table_name;

SQL> TRUNCATE TABLE 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.

SQL Statement:

Edit the SQL statement and click     to see the result, or  

Result:
The Database includes:
The Database includes:

TablenameRecord
Customers91
Categories8
Employees10
OrderDetails518
Orders196
Products77
Shippers3
Suppliers29