Table of contents

Introduction

1.1.

DROP TABLE

1.2.

DELETE TABLE

Syntax

2.1.

DROP Command:

2.2.

DELETE Command:

3.1.

Example

3.1.1.

DROP Command:

3.1.2.

DELETE Command:

FAQs

Key Takeaways

Last Updated: Mar 27, 2024

DROP AND DELETE A TABLE IN SQL

Author Prashant Singh

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

DROP and DELETE Table in SQL are the commands to remove the table with all its data and delete the data of that specific table, respectively.

So let’s learn about Drop and Delete statements one by one.

DROP TABLE

DROP table query in SQL removes one or more table definitions and all the data, indexes, triggers, constraints, and permission specifications for those tables in the database.

It is a DDL( Data Definition Language) command. We cannot roll back this operation, i.e., once the Drop statement is used, we can not recover data of that table from the database.

DELETE TABLE

A DELETE query in SQL can delete all the data from the table in the database. We can use the WHERE clause with the DELETE command to delete specific records from the table.

It is a DML(Data manipulation language ) command. The DELETE command when used with the WHERE clause deletes one row at a time and records the entry in the transaction log for each deleted row of the table. To use the Delete command, we need DELETE permission on the table.

NOTE: In some places, Rows are referred to as Tuples.

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Syntax

Syntax of Drop and Delete command are as follows

DROP Command:

DROP TABLE table_name;

DELETE Command:

DELETE FROM table_name WHERE condition;

Example

Let’s take an example that shows how to use these Drop and Delete commands each.

DROP Command:

Suppose there is a table named customers in the database, and we want to delete this table completely. So, we will use the given command.

Given below is the Customer table in a database named “Customers”.

CustomerID	CustomerName	City	Contact	PinCode	Country
1	Nayan Prakash	Mumbai	7256984661	402086	India
2	Akash Dixit	Noida	9965485234	210451	India
3	Anjali Singh	Gurgaon	9886542498	304056	India
4	Ayush Mishra	Delhi	7554655646	206206	India
5	Raman Goyal	Kanpur	7412336954	208002	India

The following SQL statement will also completely delete all the table and table definitions records.

DROP TABLE customers;

Now, if we try to access this table, we will get the following error

ERROR 1146 (42S02): Table ‘Customers’ does not exist.

Thus, all the records associated with the Customers table will be deleted as well as its table definition will be deleted from the database.

DELETE Command:

Let's say below is the “Customers” table in a database.

CustomerID	CustomerName	City	Contact	PinCode	Country
1	Nayan Prakash	Mumbai	7256984661	402086	India
2	Akash Dixit	Noida	9965485234	210451	India
3	Anjali Singh	Gurgaon	9886542498	304056	India
4	Ayush Mishra	Delhi	7554655646	206206	India
5	Raman Goyal	Kanpur	7412336954	208002	India

The following SQL statement will delete the customer “Akash Dixit” from the “Customers” table.

DELETE FROM Customers WHERE CustomerName=’Akash Dixit’ ;

Now the table will look like this:

CustomerID	CustomerName	City	Contact	PinCode	Country
1	Nayan Prakash	Mumbai	7256984661	402086	India
3	Anjali Singh	Gurgaon	9886542498	304056	India
4	Ayush Mishra	Delhi	7554655646	206206	India
5	Raman Goyal	Kanpur	7412336954	208002	India

Must Read, sql functions and SQL EXCEPT

FAQs

How can we delete all the rows from the table?

To delete all the rows, we will use the following command

DELETE FROM table_name;

Let's say the following was the "Customers" table.

CustomerID	CustomerName	City	Contact	PinCode	Country
1	Nayan Prakash	Mumbai	7256984661	402086	India
2	Akash Dixit	Noida	9965485234	210451	India
3	Anjali Singh	Gurgaon	9886542498	304056	India
4	Ayush Mishra	Delhi	7554655646	206206	India
5	Raman Goyal	Kanpur	7412336954	208002	India

After using the Delete statement

DELETE FROM Customers;

Now, the table will be

CustomerID

CustomerName

City

Contact

PinCode

Country

This will remove all the rows from the table, but its table definition will not be deleted.

2. After using DROP Command, is the space occupied by the table freed or not?

Drop statement frees the space occupied by the table in memory. Still, in the Delete statement, memory is not freed even if we delete all the rows because the Table definition remains there.

3. What is DDL( Data Definition Language)?

DDL (Data Definition or Data Description Language ) is a syntax for creating and modifying the database objects such as tables, indices, and users in the database. DDL statements are similar to programming languages for defining the data structures, especially schemas of the database.

Some examples of DDL statements are Create, Alter and Drop.

4. What is DML(Data Manipulation Language)?

A data manipulation language (DML) is a syntax used for adding (or inserting), deleting (or removing), and modifying (or updating) the data in a database.

DML allows us to manage the stored data in the database (It is used to populate and manipulate data ). It affects one or more rows.

Key Takeaways

In this blog, we learned about Drop and Delete statements in SQL queries.

We begin with learning the syntax of these statements and how to use them in queries with various examples. We also learned about the difference between Drop and Delete statements.

Visit here to learn more about different topics related to database management systems.