In SQL, we use the DELETE statement to delete row(s) from a database table. For example,
DELETE FROM Customers
WHERE customer_id = 5;
Here, the SQL command will delete a row from the Customers table where customer_id is 5.
Delete all Rows in a Table
The WHERE clause determines which rows to delete. However, we can delete all rows at once if we omit the WHERE clause. For example,
DELETE FROM Customers;
Here, the SQL command deletes all rows from a table.
Note: Be careful when you use DELETE. Records may lose permanently if the database is not backed up.
Truncate Table in SQL
The TRUNCATE TABLE clause is another way to delete all rows from a table at once. For example,
TRUNCATE TABLE Customers;
Here, the SQL command does exactly the same thing the above command does.
Note: The TRUNCATE clause doesn't support the WHERE clause.
Delete Vs Truncate
The main difference between both statements is that DELETE FROM statement supports WHERE clause whereas TRUNCATE does not.
That means, we can delete single or multiple rows using the DELETE FROM statement while the TRUNCATE statement deletes all records from the table at once.
We can mimic the TRUNCATE statement with DELETE FROM statement by omitting the WHERE clause. For example,
DELETE FROM Customers;
is similar to,
TRUNCATE TABLE Customers;