DROP TABLE

DROP TABLE

What it means

DROP TABLE permanently removes a table’s structure and all its data from the database — unlike DELETE, which removes rows but keeps the table, and unlike TRUNCATE, which empties a table but keeps its structure. There’s no built-in “undo” beyond restoring from a backup.

Examples

DROP TABLE Orders;

-- Guard against errors if the table might not exist
DROP TABLE IF EXISTS Orders;

-- Dropping multiple tables at once
DROP TABLE IF EXISTS Orders, OrderItems;

Common mistake

Confusing DROP TABLE, TRUNCATE TABLE, and DELETE FROM — they sound similar but have very different blast radii: DELETE removes some or all rows but the table remains; TRUNCATE empties all rows but keeps the table structure; DROP removes the table entirely, structure included.

DROP TABLE Orders;        -- table is GONE -- structure, indexes, constraints, everything
TRUNCATE TABLE Orders;    -- table still exists, empty, structure/indexes intact
DELETE FROM Orders;       -- table still exists, rows removed (optionally filtered), structure intact

Quick practice

1. What’s the key difference between DROP TABLE and TRUNCATE TABLE?

   Answer

   DROP TABLE removes the table's structure entirely; TRUNCATE TABLE empties all rows but keeps the table (and its columns, indexes, constraints) intact.

2. What does DROP TABLE IF EXISTS Orders; do differently from a plain DROP TABLE Orders;?

   Answer

   It doesn't raise an error if the Orders table doesn't exist — useful in scripts that need to run safely whether or not the table is already present.

3. After dropping a table, what’s the standard way to recover it if it turns out to have been a mistake?

   Answer

   Restore from a database backup — there's no built-in "undo" for DROP TABLE itself.