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;  

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.

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.

Let’s see an “employee” table.

Emp_id	Name	Address	Salary
1	Aryan	Allahabad	22000
2	Shurabhi	Varanasi	13000
3	Pappu	Delhi	24000

Execute the following query to truncate the table:

TRUNCATE TABLE employee;  

Difference b/w DROP and TRUNCATE statements

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.