Home/Tutorials/Database Management Systems/SQL - DELETE Query

0 of 3 saved

Database Management Systems3 min read

SQL - DELETE Query

Read this chapter, write the program on this page, then pass a short check to open the next one.

Delete SQL statement is used to delete any data from a table. In this tutorial we will learn how to delete data using Delete SQL statement in RDBMS.

Using DELETE SQL command

Data Manipulation Language (DML) provides SQL Commands that are used for managing data in the database. DML commands are not auto-committed. It means changes made by DML Commands are not permanent to the database, it can be rolled back.

Data Manipulation Language supports three main commands. These are: INSERT, UPDATE and DELETE commands. In this article, we will discuss only the DELETE statement in SQL.

SQL DELETE Statement

SQL DELETE command, as the name suggests, is used for deleting or removing one or more records of a table in a database. For example, you can delete any comment or message on social media. This is done using the DELETE command.

Syntax:

DELETE FROM table_name;

Let us consider a table 'student' with the following three fields and three records.

SQL DELETE Statement

Using the DELETE statement alone can delete the whole record or data of the table. In order to delete a particular record, use the WHERE clause with the DELETE statement. It will only delete the record that satisfies the WHERE condition. We will look into the WHERE clause in detail in another article.

Now, let us discuss different use cases of the DELETE command in SQL using different examples.

Example: Delete all Records from a Table

The below SQL query will delete all the records from the table student and if you try to display the records, it will print nothing. This query does not use the WHERE clause.

DELETE FROM student;

Example: Delete a Particular Record from a Table

From the given student table, if we want to delete a single record, we can use the WHERE clause to provide a condition in our DELETE statement. The below SQL query deletes the record where id equals 103.

DELETE FROM student 
WHERE id = 103;

Output:

SQL DELETE Statement

DELETE vs TRUNCATE vs DROP

These three SQL commands look similar and work similarly but have some major differences that make them unique. In order to understand the differences among them, refer to SQL TRUNCATE and SQL DROP articles.

DELETE TRUNCATE DROP

The delete command only deletes all the rows from a table keeping the structure of the table.

The truncate command not only deletes all the rows from a table but also re-initializes the table (like a newly created table).

It deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid.

It does not free the space containing by the table.

It frees the containing space.

 It frees the containing space.

It is a DML command.

It is a DDL command.

It is a DDL command.

Changes made using DELETE can be rolled back.

Changes made using TRUNCATE cannot be rolled back.

 Changes made using DROP cannot be rolled back.
It can be used with the WHERE clause to remove specific data. It cannot be used with the WHERE clause to remove specific data. It cannot be used with the WHERE clause to remove specific data.

For Example: If you have a table with 10 rows and an auto_increment primary key, and if you use the DELETE command to delete all the rows, it will delete all the rows, but will not re-initialize the primary key, hence if you will insert any row after using the DELETE command, the auto_increment primary key will start from 11. But in the case of the TRUNCATE command, the primary key is re-initialized, and it will again start from 1.

Conclusion

In this article, we discussed the DELETE statement in SQL with examples. We also discussed the differences between DROP, TRUNCATE and DELETE commands.