MySQL DROP TABLE Statement

  1. Overview
  2. MYSQL
  3. MySQL Database
  4. MySQL DROP TABLE Statement

The DROP TABLE statement is used to remove an existing table from the database. When a table is dropped, all the data, the table structure, and any associated constraints or indexes are permanently removed.

Syntax

DROP TABLE table_name;
  • table_name: The name of the table you want to delete.

Example

To delete a table called Employees:

DROP TABLE Employees;

Note: Be very cautious when using the DROP TABLE command because it completely removes the table and all the data within it. This action cannot be undone unless you have a backup.

MySQL TRUNCATE TABLE Statement

The TRUNCATE TABLE statement is used to delete all the data in a table, but the table structure (schema) remains intact. Unlike DROP, TRUNCATE does not remove the table, just its content.

Syntax

TRUNCATE TABLE table_name;
  • table_name: The name of the table from which data should be removed.

Example

To remove all records from the Employees table but keep the table itself:

TRUNCATE TABLE Employees;

Note:

  • The table structure, indexes, and constraints remain unaffected by the TRUNCATE command.
  • TRUNCATE is usually faster than DELETE because it does not generate individual row deletion logs (in most databases).
  • Some databases treat TRUNCATE as a DDL command, meaning it cannot be rolled back if not within a transaction.

Key Differences Between DROP and TRUNCATE

  • DROP removes both the table structure and data completely from the database.
  • TRUNCATE only deletes the data in the table, leaving the table structure intact, allowing the table to be reused.
Was this article helpful?