Back to articles list June 24, 2020 - 6 minutes read TRUNCATE TABLE vs. DELETE vs. DROP TABLE: Removing Tables and Data in SQL Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: sql learn sql data engineering What’s the difference between truncating, deleting, and dropping a table in SQL? Find out in this article. There are a lot of ways to delete data in SQL, including the DELETE, TRUNCATE TABLE and DROP TABLE commands. Which one should you use in a given situation? In this article, you’ll learn the syntax of each command in different database engines like MySQL, PostgreSQL, SQL Server, and Oracle. And you’ll understand the DROP TABLE vs. DELETE vs. TRUNCATE TABLE debate. Let’s get started! DELETE DELETE is a DML (Data Manipulation Language) command. This command removes records from a table. It is used only for deleting data from a table, not to remove the table from the database. You can delete all records with the syntax: DELETE FROM name_table; Or you can delete a group of records using the WHERE clause: DELETE FROM name_table WHERE col=value; If you’d like to remove all records from a given table, use DELETE FROM followed by the table name. Notice that there aren’t any column names in this syntax; you’re removing all records, not the data in selected columns. If you want to remove specific records, use WHERE with filtering conditions, as in the second example. Let’s use these commands in an example. Here’s the table product: idnameprice 1milk2.40 2bread3.68 3butter5.55 4sugar2.88 This query ... DELETE FROM product; … removes all the data in the table product. After this query, the table product will be empty. But the WHERE query ... DELETE FROM product WHERE price<2.90; … deletes only the records for milk and sugar, because their prices are lower than $2.90. (Milk is $2.40 and sugar is $2.88.) Now the table product only has records with prices higher than $2.90: idnameprice 2bread3.68 3butter5.55 How does DELETE work? If you don’t want to remove table structure or you’re only deleting specific rows, use the DELETE command. It can remove one, some, or all rows in a table. DELETE returns the number of rows removed from the table. However, DELETE uses a row lock during execution and can be rolled back. Every deleted row is locked, so it will require a lot of locks if you’re working in a large table. DELETE also keeps the auto-increment ID in the table. If you remove the last record in the table with ID=20 and then add a new record, this record will have ID=21 – even though the record immediately before it will be ID=19. DELETE can be executed by triggers. A trigger can be called before, after, or instead of the DELETE operation. It can be executed for any row change or when all rows are removed. Removing rows in another table can also trigger DELETE. Of course, to use the DELETE command you need DELETE permission for that table. TRUNCATE TABLE TRUNCATE TABLE is similar to DELETE, but this operation is a DDL (Data Definition Language) command. It also deletes records from a table without removing table structure, but it doesn’t use the WHERE clause. Here’s the syntax: TRUNCATE TABLE table_name; If you use this command, all rows in this table will be removed. The following query ... TRUNCATE TABLE product; … deletes all records stored in the table product. How does TRUNCATE TABLE work? Be careful using this command. TRUNCATE transactions can be rolled back in database engines like SQL Server and PostgreSQL, but not in MySQL and Oracle. TRUNCATE is faster than DELETE, as it doesn't scan every record before removing it. TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE. Unlike DELETE, TRUNCATE does not return the number of rows deleted from the table. It also resets the table auto-increment value to the starting value (usually 1). If you add a record after truncating the table, it will have ID=1. Note: In PostgreSQL, you can choose to restart or continue the auto-increment value. Oracle uses a sequence to increment values, which is not reset by TRUNCATE. Of course, you need permission to use TRUNCATE TABLE. In PostgreSQL, you need the privilege TRUNCATE; in SQL Server, the minimum permission is ALTER table; in MySQL, you need the DROP privilege. Finally, Oracle requires the DROP ANY TABLE system privilege to use this command. You can learn more in the course “The Basics of Creating Tables in SQL”, which is part of our Data Engineering Path. DROP TABLE The DROP TABLE is another DDL (Data Definition Language) operation. But it is not used for simply removing data from a table; it deletes the table structure from the database, along with any data stored in the table. Here is the syntax of this command: DROP TABLE table_name; All you need after DROP TABLE is the name of the table you want to delete. For example, if you’d like to remove the entire product table from the database, you’d write: DROP TABLE product; This removes all data in the table product and the structure of the table. How does DROP TABLE work? The DROP TABLE operation removes the table definition and data as well as the indexes, constraints, and triggers related to the table. This command frees the memory space. No triggers are fired when executing DROP TABLE. This operation cannot be rolled back in MySQL, but it can in Oracle, SQL Server, and PostgreSQL. In SQL Server, DROP TABLE requires ALTER permission in the schema to which the table belongs; MySQL requires the DROP privilege; Oracle the requires the DROP ANY TABLE privilege. In PostgreSQL, users can drop their own tables. DROP TABLE vs. DELETE TABLE vs. TRUNCATE TABLE in SQL Which cases call for DROP TABLE? When should you use TRUNCATE or opt for a simple DELETE? We’ve prepared the table below to summarize the properties of each command: DELETETRUNCATEDROP TypeDMLDDLDDL Uses a lockTable lockRow lockTable lock Works in WHEREYesNoNo Removes ...One, some, or all rows in a table.All rows in a table.Entire table structure: data, privileges, indexes, constraints, triggers. Resets ID auto-incrementNoMySQL: Yes Oracle: NoPostgreSQL: User decidesSQL Server: YesDoesn’t apply RollbackYesMySQL: NoOracle: NoPostgreSQL: YesSQL Server: YesMySQL: NoOracle: YesPostgreSQL: Yes SQL Server : Yes Transaction loggingEach rowWhole table (minimal)Whole table (minimal) Works with indexed viewsYesNoNo Space requirementsMore spaceLess spaceMore space Fires triggersYesNoNo SpeedSlowFastestFaster Which operation is best for which use case? To remove specific rows, use DELETE. To remove all rows from a large table and leave the table structure, use TRUNCATE TABLE. It’s faster than DELETE. To remove an entire table, including its structure and data, use DROP TABLE. You can learn more about creating database structures in our Data Engineering Path. In SQL, DELETE, TRUNCATE TABLE, and DELETE TABLE all have pluses and minuses. Hopefully, this article has helped you understand when and how to use each command. Tags: sql learn sql data engineering You may also like How to Recognize SQL Text Data Type Learn the characteristics of every SQL text data type. Discover Oracle data type for large text, PostgreSQL fixed-size character text and more. Read more Understanding Numerical Data Types in SQL As you start learning with LearnSQL.com, you start to understand SQL's different data types. In this article, we will cover the SQL numeric data type. Read more Referential Constraints and Foreign Keys in MySQL Foreign keys and referential constraints allow you to set relationships between tables and modify database engine’s actions. See how to use it in MySQL. Read more Microsoft SQL Server Pros and Cons In this entry, we’ll go over the pros and cons of Microsoft SQL Server and evaluate the platform from the perspective of a company and a data specialist. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.