DROP TABLE

At the end of the season you might have temporary tables you no longer need, like auction_draft. DROP TABLE removes a table from the database completely.

Definition

DROP TABLE is a DDL statement that permanently deletes a table from the database — its structure (columns, data types, constraints, indexes) AND all the rows stored in it.

• Permanently — there is no recycle bin. Unless you have a backup, it's gone.
• Structure AND data — after DROP, the table doesn't exist anymore. SELECT * FROM players would fail with "Invalid object name 'players'."

Syntax

DROP TABLE table_name;

-- Only if it exists (SQL Server 2016+, MySQL, PostgreSQL)
DROP TABLE IF EXISTS table_name;

-- Several tables at once
DROP TABLE table1, table2;

Example — DROP TABLE players

Before: the players table exists with columns id, name, team, country, runs.

DROP TABLE players;

After: Table deleted — players no longer exists.

Step by step: 1️⃣ DROP TABLE → 2️⃣ players (gone!)

Warning: Deletes the table AND all its data. This cannot be undone!

Example — proving the table is gone

CREATE TABLE auction_draft (
    player VARCHAR(50),
    price_cr DECIMAL(5,2)
);

DROP TABLE auction_draft;

SELECT * FROM auction_draft;   -- ❌ Invalid object name 'auction_draft'

DROP vs DELETE — do not mix them up

(Full comparison with TRUNCATE is in the DML chapter.)

When can DROP fail?

• The table doesn't exist → "Cannot drop the table 'x', because it does not exist or you do not have permission." Use IF EXISTS.
• Another table's FOREIGN KEY references it → SQL Server refuses. Drop the child table (or its foreign key) first.
• No permission — you need ALTER permission on the schema or CONTROL permission on the table.

Common mistakes

• ❌ Running DROP TABLE on the wrong database/server (e.g. production instead of test). ✅ Check which database you are connected to (USE ipl_db;) before running DDL.
• ❌ Using DROP when you only wanted to empty the table. ✅ Use DELETE (or TRUNCATE) to remove rows and keep the table.
• ❌ Dropping a parent table that has child tables. ✅ Drop children first, or remove the foreign key.
• ❌ Writing DROP players. ✅ DROP TABLE players — the object type is required.

SQL Server vs MySQL / PostgreSQL

DROP TABLE IF EXISTS works in SQL Server 2016 and later, MySQL and PostgreSQL. On older SQL Server versions:

IF OBJECT_ID('dbo.auction_draft', 'U') IS NOT NULL
    DROP TABLE dbo.auction_draft;

Interview tip

Q: Can you roll back a DROP TABLE? A: In SQL Server, yes — but only if it was run inside an explicit transaction (BEGIN TRANSACTION … ROLLBACK) that hasn't been committed yet. Once committed (or in MySQL, where DDL auto-commits), the only way back is restoring a backup.