How to Delete a Column in SQL?

5 mins read15.7K Views Comment

Updated on Aug 22, 2024 08:49 IST

Sometimes, in the dataset, you have some columns that are not of your use, but when you run a query, it will also get executed, which leads to an increase in your execution time. In SQL, you can use ALTER TABLE and DROP commands to delete a column in the dataset. This article will briefly discuss how to delete a column in SQL.

Deleting a column in a relational database using SQL involves a crucial aspect of database management and maintenance. As databases evolve and data needs change, removing unnecessary or redundant columns arises. Whether you’re streamlining your data structure, complying with data privacy regulations, or optimizing database performance, understanding the process of deleting a column is essential. This blog will explore the steps and considerations involved in deleting a column from a table using SQL, ensuring you can efficiently manage your database schema while preserving data integrity.

. To delete a column in SQL, we use the ALTER TABLE command to change the table’s structure with the DROP keyword to delete the specific column.

Syntax:

MS SQL Server

ALTER TABLE table_name DROP column_name1, column_name2...;
Copy code

Oracle database

ALTER TABLE table_name DROP(column_name1, column_name2...);
Copy code

MySQL and PostgreSQL databases.

ALTER TABLE table_name DROP column_name1, DROP column_name2...;
Copy code

Must Read: Introduction to SQL

Must Read: SQL ACID Properties

In the above syntaxes:

First, we specify the table name after the ALTER TABLE command from which the column is to be removed.
Then we specify the column name that is to be removed after the DROP keyword.

Checkout the Top Online SQL Courses and Certifications

Note: For the purpose of demonstration, we will be working with a MySQL database in this article.

Now that we know the syntax to delete columns from a database, let’s set up one table and use the above syntax on it to delete columns in that table.

To create a simple table with 5 columns, use the following SQL statement:

-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  standard INTEGER NOT NULL,
  section TEXT NOT NULL
);
Copy code

Now let’s insert some data to our students table using the below SQL commands:

-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M',5,'A');
INSERT INTO students VALUES (2, 'Joanna', 'F',7,'B');
INSERT INTO students VALUES (3, 'Mike', 'F',8,'C');
INSERT INTO students VALUES (4, 'Harvey', 'F',11,'D');
INSERT INTO students VALUES (5, 'Donna', 'F',12,'A');
Copy code

Now we can view the complete table with its data using the below command:

SELECT * FROM students;
Copy code

Output:

So currently we have 5 columns as listed below:

id
name
gender
standard
section

Now let’s jump into some examples where we will be deleting columns from the students table that we just created above.

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

Example 1: Deleting a Single column.

In this example, we will delete the section column from the students table. For that, we can use the below SQL command:

ALTER TABLE students DROP section;
Copy code

After executing the above statement, use the below statement to view the table and its structure;

Output:

Must Read: Subqueries in SQL

Must Read: SQL CREATE TABLE

Example 2: Deleting multiple columns.

In this example, we will delete the gender and standard columns from the students table. For this, we can use the below SQL command:

ALTER TABLE students DROP gender, standard;
Copy code

After executing the above statement, use the below statement to view the table and its structure;

SELECT * FROM students;
Copy code

Output:

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

Conclusion:

In this article, we have managed to learn the process of deleting single or multiple columns from a table. We have also established the use of the ALTER command while changing the structure of a table. Then, only we can use the DROP keyword actually to delete a column(s).
Hope you will like the article.
Keep Learning!!
Keep Sharing!!

Please Checkout More SQL Blogs

How to Find Nth Highest Salary in SQL

Finding out the N’th highest salary from a table is one of the most frequently asked SQL interview questions. In this article, we will discuss four different approaches to find...read more

Read Later

Order of Execution in SQL

An SQL query comprises of various clauses like SELECT, FROM, WHERE, GROUPBY, HAVING, and ORDERBY clauses. Each clause has a specific role in the query. The correct order of execution...read more

Read Later

How to Find Second Highest Salary in SQL

It's crucial to master SQL queries for managing and analyzing databases. This article focuses on finding the second-highest salary in SQL, which is a common yet important task in database...read more

Read Later

Using Partition By SQL Clause

The Partition By SQL clause is a subclause of OVER clause that is used in every invocation of window functions such as MAX(), RANK() and AVG(). In SQL, the “PARTITION...read more

Read Later

Top 30 SQL Query Interview Questions

Structured Query Language or most commonly known as SQL is used on a daily basis to handle, manipulate and analyze relational databases.

Read Later