How to Delete Column in SQL (original) (raw)
Last Updated : 23 Jul, 2025
In SQL, deleting a column from an existing table is a straightforward process, but it's important to understand the implications and the correct syntax involved. While there is no direct **DELETE COLUMN command in SQL, we can achieve this by using the **ALTER TABLE command combined with **DROP COLUMN.
In this article, we'll explain **how to delete a column in SQL, providing the **syntax, practical **examples, and best practices for safely removing unnecessary columns from your database.
Understanding Process of Deleting a Column in SQL
**Deleting a column in SQL refers to the process of permanently removing a specific column from a table. This is done using the DROP COLUMN command, which is part of **Data Definition Language (DDL) operations. Deleting columns that are no longer needed can significantly improve our **database's performance, reduce **storage consumption, and help maintain a clean and efficient **schema.
Why Deleting a Column in SQL is Important
- **Data Optimization: Dropping columns that are no longer needed reduces the **size of our table and optimizes storage space.
- **Improved Performance: Removing unnecessary columns can help improve the performance of **SQL queries since the database engine doesn't have to retrieve and process unnecessary data.
- **Cleaner Database Schema: It helps in maintaining a **clean and organized schema, especially when dealing with evolving data models.
**Syntax
ALTER TABLE Name_of_the_table
DROP COLUMN Name_of_the_column ;
**Key Terms
ALTER TABLE: This clause is used to modify the structure of an existing table.DROP COLUMN: This specifies that a column should be deleted from the table.table_name: The name of the table from which the column will be removed.column_name: The name of the column that you want to delete.
Example 1: Deleting a Column from a Table
Let's walk through a practical example to demonstrate how to delete a column in SQL. In this scenario, we will create a table named **GeeksforGeeks. This table consists of **rank, **name, **age, **monthly **score, questions solved, and overall score as the columns of the table **GeeksforGeeks.
Step 1: Create the Sample Table
CREATE TABLE GeeksforGeeks (
rank int,
name varchar(100),
age int,
monthly_score int,
questions_solved int,
overall_score int
);
Step 2: Insert Data into the Table
INSERT INTO GeeksforGeeks(rank,name,age,monthly_score,questions_solved,overall_score)
VALUES (01, 'Vishu', 20 ,272 ,415 ,1448);
INSERT INTO GeeksforGeeks(rank,name,age,monthly_score,questions_solved,overall_score)
VALUES (02, 'Kuntal', 20 ,271 ,410 ,1446);
INSERT INTO GeeksforGeeks(rank,name,age,monthly_score,questions_solved,overall_score)
VALUES (03, 'Priyam', 20 ,270 ,408 ,1440);
INSERT INTO GeeksforGeeks(rank,name,age,monthly_score,questions_solved,overall_score)
VALUES (04, 'Shailesh', 21 ,268 ,407 ,1438);
INSERT INTO GeeksforGeeks(rank,name,age,monthly_score,questions_solved,overall_score)
VALUES (05, 'Avirup', 20 ,267 ,406 ,1437);
INSERT INTO GeeksforGeeks(rank,name,age,monthly_score,questions_solved,overall_score)
VALUES (06, 'Neeraj', 21 ,265 ,405 ,1436);
Step 3: View the Data in the Table
SELECT * FROM GeeksforGeeks;
**Output
GeeksforGeeks Table
**Explanation:
In the "**GeeksforGeeks" table, the "**age" column holds no value to the table data. We can remove it using the **DROP COLUMN command without affecting the rest of the table.
**Step 4: Delete the age Column
If the age column is no longer needed, it can be dropped using the DROP COLUMN command.
ALTER TABLE GeeksforGeeks
DROP COLUMN age;
**Step 5: Verify the Changes
Check the table structure and data after dropping the column.
SELECT * FROM GeeksforGeeks;
**Output
| rank | name | monthly_score | questions_solved | overall_score |
|---|---|---|---|---|
| 1 | Vishu | 272 | 415 | 1448 |
| 2 | Kuntal | 271 | 410 | 1446 |
| 3 | Priyam | 270 | 408 | 1440 |
**Explanation:
The "**age" column has been successfully removed from the table without affecting the other columns.
Example 2: Dropping Multiple Columns
In SQL, we can also **drop multiple columns in a single query by separating column names with commas. This method simplifies queries by eliminating the need to write multiple ALTER TABLE statements, making the operation more efficient.
Step 1: Create a Table
CREATE TABLE Products (
product_id INT,
product_name VARCHAR(50),
category VARCHAR(50),
price DECIMAL(10, 2),
stock_quantity INT
);
Step 2: Insert Sample Data
INSERT INTO Products (product_id, product_name, category, price, stock_quantity)
VALUES
(1, 'Laptop', 'Electronics', 800.00, 50),
(2, 'Desk', 'Furniture', 150.00, 20),
(3, 'Chair', 'Furniture', 85.00, 100);
Step 3: Drop "category" and "stock_quantity" Columns
ALTER TABLE Products
DROP COLUMN category, stock_quantity;
Step 4: View the Updated Table
SELECT * FROM Products;
**Output
| product_id | product_name | price |
|---|---|---|
| 1 | Laptop | 800.00 |
| 2 | Desk | 150.00 |
| 3 | Chair | 85.00 |
**Explanation:
Both the "**category" and "**stock_quantity" columns have been successfully removed, leaving only the relevant columns in the table.
**Important Notes About Using DROP COLUMN in SQL
- **Irreversible Change: Once we drop a column using the **DROP COLUMN command, the data is permanently lost, and the column cannot be recovered.
- **Foreign Key Constraints: If the column being dropped is referenced by foreign key constraints, we will need to drop those constraints before dropping the column.
- **Large Tables: Dropping a column in a large table might take some time, depending on the size of the table and the database system.
Conclusion
The **DROP COLUMN command is a useful tool for database administrators and developers to clean up unnecessary data and **optimize table structures in SQL. By removing **redundant or **irrelevant columns, we can reduce **storage space, improve query performance, and maintain a **cleaner schema. However, it's essential to carefully review the column before **dropping it since the operation is **permanent.