SQL Delete and Truncate Rows

In this tutorial, we'll learn to delete rows from a table in SQL with the help of examples.

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.

How to delete a row in SQL
Example: SQL DELETE command

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;
Did you find this article helpful?