← SQL roadmap

Topic 5 of 52 · Beginner

ALTER TABLE – DROP COLUMN

Permanently remove a column and all its values.

01ALTER TABLE – DROP COLUMN

Module 02 – Database & Table Management (DDL)

02What is it?

DROP COLUMN deletes a column from the table definition. Every value stored in that column, in every row, is gone permanently. The rows themselves remain.

03Why do we need it?

Unused columns waste storage, confuse users and may hold sensitive data that must be removed (for example, an old PAN or Aadhaar column after a privacy review).

04Syntax

ALTER TABLE table_name DROP [COLUMN] column_name;

05Parameter explanation

column_name
The column to remove. It must exist (MySQL has no IF EXISTS here).
COLUMN
Optional keyword.

06Sample database

Every example and exercise below starts from a fresh copy of this data.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  dept_id   INT,
  salary    DECIMAL(10,2),
  hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
 (1,'Asha',10,85000,'2021-04-12'),
 (2,'Ravi',20,52000,'2022-01-03'),
 (3,'Meena',10,97000,'2019-09-20'),
 (4,'Karan',30,46000,'2023-06-15'),
 (5,'Divya',20,61000,'2020-11-30');

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.MySQL checks hire_date exists and is not the table's only column.
  2. 2.The column is removed from the definition and its data is discarded.
  3. 3.Indexes using only that column are dropped too. The 5 rows still exist with 4 columns.

09Before and after output

Generated live by running the basic example against the sample database.

Running on the SQL engine…

10Intermediate coding example

Intermediate example

11Real-world business scenario

During a schema clean-up, a data engineer confirms (via query logs) that legacy_code hasn't been read in 6 months, takes a backup, then runs ALTER TABLE ... DROP COLUMN legacy_code.

12Common mistakes

ALTER TABLE employees DROP COLUMN phone;

phone doesn't exist → 'Can't DROP; check that column exists'.

Check columns first with DESCRIBE employees;

ALTER TABLE employees DELETE COLUMN salary;

DELETE is a DML command for rows, not columns.

ALTER TABLE employees DROP COLUMN salary;

UPDATE employees SET salary = NULL; -- thinking it removes the column

This only empties values; the column still exists.

ALTER TABLE employees DROP COLUMN salary;

13Important comparisons

CommandRemoves
ALTER TABLE t DROP COLUMN cOne column (all its values)
DELETE FROM t WHERE ...Some rows
TRUNCATE TABLE tAll rows, keeps table
DROP TABLE tThe whole table

14Practical exercises

Beginner1. Drop hire_date from employees.

Reveal solution (15)
Solution · run it

Four columns remain.

Beginner2. Drop salary and confirm the remaining column names.

Reveal solution (15)
Solution · run it

In MySQL you would run DESCRIBE employees.

Beginner3. Add a column notes TEXT, then drop it.

Reveal solution (15)
Solution · run it

Table returns to its original shape.

Intermediate4. Copy salary into a backup table, then drop it from employees.

Reveal solution (15)
Solution · run it

Always back up before destructive DDL.

Intermediate5. Drop two columns: hire_date and salary.

Reveal solution (15)
Solution · run it

MySQL also accepts DROP COLUMN a, DROP COLUMN b in one statement.

Intermediate6. Show that a query using a dropped column fails.

Reveal solution (15)
Solution · run it

Expected error: no such column.

Advanced7. Restore a dropped salary column from salary_backup.

Reveal solution (15)
Solution · run it

Recovery is only possible because of the backup.

Advanced8. Count how many non-NULL values would be lost before dropping hire_date.

Reveal solution (15)
Solution · run it

A pre-check quantifies the impact (here 5).

16Key takeaways

  • DROP COLUMN permanently deletes a column's data.
  • Rows survive; only that column goes.
  • Back up and check dependencies (views, code) first.
  • DDL auto-commits in MySQL — no ROLLBACK.

17Knowledge check

1. After DROP COLUMN, how many rows does the table have?

2. Can you ROLLBACK a DROP COLUMN in MySQL?

3. Which statement removes a column?

Interview questions

What happens to an index when its column is dropped?

A single-column index on it is removed; a composite index loses that column, and if no columns remain the index is dropped.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “ALTER TABLE – DROP COLUMN” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.