Understanding the Difference Between TRUNCATE, DELETE, and DROP in SQL

1. Overview

In managing the contents of database tables, data manipulation commands play a crucial role within SQL. Two frequently used statements, TRUNCATE and DELETE, are responsible for removing data from tables. Despite appearing similar, these statements serve distinct purposes and come with different implications.

In this tutorial, we’ll delve into the differences between TRUNCATE and DELETE statements in SQL. We’ll explore their syntax, usage scenarios, and the tabular disparities between them.

Notably, we’re going to perform this operation using the Postgres database. However, the same process applies to other databases as well.

2. What Is a TRUNCATE Statement

The TRUNCATE statement in SQL is a data manipulation statement that quickly removes all records from a table.

The TRUNCATE statement doesn’t utilize any conditions like the WHERE clause does to define conditions. Therefore, it’s best to use it only when all of the data in the target table is safe to be removed.

Moreover, TRUNCATE locks the entire table and its pages, whereas DELETE locks individual rows. This means it only records the removal of page allocations, making it much faster than DELETE.

When we execute the TRUNCATE statement, the operation is committed automatically. Additionally, the identification is reverted to its seed value.

2.1. Syntax of the TRUNCATE Statement

The syntax of the TRUNCATE statement is straightforward:

TRUNCATE TABLE table_name;

In the example above, the table_name specifies the name of the table from which you want to remove all records.

2.2. TRUNCATE Usage Example

First, let’s create a sample table named Department with the following SQL query:

CREATE TABLE Department
(
    id INT PRIMARY KEY NOT Null,
    name VARCHAR (50),
    code VARCHAR (4),
    UNIQUE (id)
);

Next, we populate the Department table with some sample data:

INSERT INTO Department (id, name, code) VALUES
  (1, 'Computer Science', 'CS'),
  (2, 'Electronics and Communications', 'EC'),
  (3, 'Mechanical Engineering', 'ME'),
  (4, 'Civil Engineering', 'CE'),
  (5, 'Mathematics', 'MA');

We can check the created table with this SQL query:

SELECT * FROM Department;

We can then inspect the result set:

Now let’s run the TRUNCATE statement to delete all the records from the Department table:

TRUNCATE TABLE Department;

This command deletes all the records from the Department table but doesn’t delete the table itself.

Next, we can verify this with this SQL query:

SELECT * FROM Department;

We can see the output of the executed SQL query:

As seen above, all the records were deleted and the table still exists.

3. What Is a DELETE Statement

In contrast to the TRUNCATE statement, the DELETE statement in SQL is used to remove specific rows from a table based on specified conditions. While TRUNCATE removes all rows from a table, DELETE offers more flexibility by allowing the removal of only those rows that meet certain criteria.

3.1. Syntax of the DELETE Statement

The syntax for the DELETE statement is as follows:

DELETE FROM table_name 
  WHERE condition;

Where table_name is the name of the table from which you want to delete rows. Additionally, the condition specifies the criteria for the rows to be deleted. Note that, if the WHERE clause is omitted, all rows in the table will be deleted.

3.2. DELETE Usage Example

Let’s illustrate the usage of the DELETE statement with an example. Suppose we want to delete all records from the Department table where the code is ‘ME’ (indicating Mechanical Engineering):

DELETE FROM Department
  WHERE code = 'ME';

This query removes all rows from the Department table where the code column has the value ‘ME’.

Furthermore, we can verify the changes by querying the Department table again:

SELECT * FROM Department;

We can then have a look at the result set:

The result set displays the updated contents of the Department table, demonstrating the effects of the DELETE statement.

4. What Is a DROP Statement?

While both TRUNCATE and DELETE statements remove table content, DROP removes the entire table data and its definition, essentially purging it from the schema.

The DROP statement generally removes the indexes, constraints, permissions, and any triggers related to the dropped table. However, any related foreign keys must be removed before the table can be dropped successfully. The implementation of the removal of these keys varies across database management systems.

4.1. Syntax of the DROP Statement

Let’s look at the syntax for the DROP statement:

DROP TABLE IF EXISTS Course;

The statement above drops the table Course if it exists. If the table doesn’t exist, the statement completes without any error. Without the IF EXISTS clause, the statement might fail if the table doesn’t exist. This behavior might vary across different databases.

The DROP statement can be applied to various database objects, such as tables, databases, and views. It’s irreversible, so it’s essential to use it with caution to maintain database integrity and avoid accidental data loss.

5. Differences Between DELETE, TRUNCATE, and DROP

Let’s explore the difference between the three statements:

6. Conclusion

In this article, we explored the purpose and implications of the SQL TRUNCATE, DELETE, and DROP statements. While both TRUNCATE and DELETE statements allow removing data from tables, they differ significantly in their mechanisms and effects on the database. Furthermore, DROP removes the entire object on which it is applied, including the content and definition in the schema.

Understanding these disparities equips beginners and database administrators with the knowledge to choose the most suitable approach for their data management needs.