SQL DROP TABLE
A SQL DROP TABLE statement is used to delete a table definition and all data from a table. syntax
DROP TABLE "table_name";
e.g DROP TABLE product;
SQL DELETE TABLE
The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.
DELETE FROM table_name [WHERE condition];
But if you do not specify the WHERE condition it will remove all the rows from the table.
DELETE FROM table_name;
SQL TRUNCATE TABLE
A truncate SQL statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause. Let's see the syntax to truncate the table from the database.
TRUNCATE TABLE table_name;
For example, you can write following command to truncate the data of employee table
TRUNCATE TABLE Employee;
There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are not exactly same there are some differences between them. Here we point out them?
Difference between DELETE and TRUNCATE statements
There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified. But it does not free the space containing by the table. The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space. To delete all the rows from student table, the query should be like this:
TRUNCATE TABLE students;
Difference between DROP and TRUNCATE statement:
When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid. When you drop a table:
- Table structure will be dropped
- Relationship will be dropped
- Integrity constraints will be dropped
- Access privileges will also be dropped
On the other hand when we TRUNCATE a table, the table structure remains the same, so you will not face any of the above problems.