SQL DELETE Vs TRUNCATE Vs DROP Statement

Three functions will be discussed in this article. DELETE, DROP , TRUNCATE is one of them.

Then we'll talk about the main differences between these three functions.

SQL TRUNCATE Statement

It's also a command in the Data Definition Language (DDL).

We can't delete a single row with the "TRUNCATE" command because the WHERE clause isn't used.

The existence of all the rows of the table is lost when this command is used.

It is faster than the remove command because it deletes all the rows at once.

The deallocation table is marked with the TRUNCATE command.

This procedure removes all data from a table while ignoring a number of table constraints.

Users cannot truncate a table that is referenced as a FOREIGN KEY in another table in MySQL.

Example: This query will remove all the records from the table Book.

Truncate function delete the whole table

Sql-server,Mysql

TRUNCATE TABLE Book;

If we can fetch data from table use below query,

SELECT* FROM Customer;

When attempting to display the records from that table, the output screen displays a no data found error. This is because it deletes all of the records in that table.

The Truncate statement is the same as a DELETE statement without the WHERE clause.

The truncate command removes all of the records from a table without having to scan it. This is why it outperforms the DELETE statement.

SQL DELETE Statement

The DELETE statement in SQL is a DML command.

It's used to get rid of existing records from a table. Depending on the condition supplied in the query, we can delete a single or several records.

We have the option of deleting all of the rows at once or one by one. Using the WHERE clause, we can apply it according to the requirement or circumstance.

The WHERE clause of the DELETE statement specifies the requirements.

If we don't utilise the WHERE clause, the table will be empty and all of the records will be erased.

Example: This query will delete the record(s) from aboveBook table where field price has a value eaual to 85.

DELETE FROM BookSimple WHERE Price=85;

Output: The Output of above query is

SQL DROP Statement

The DROP statement is a Data Definition Language (DDL) command for deleting existing database items.

It allows you to delete databases, tables, views, and triggers, among other things. It's used to throw the entire table away. In SQL, a DROP command deactivates a component in a relational database management system (RDBMS).

We can drop (delete) the entire structure at once with the "DROP" command, which eliminates the schema's named elements.

The existence of the entire table is terminated or lost when this command is used.

Example: This query will remove the Book table in above example from the database.

DROP TABLE Book;

DROP Command removes the table definition and all the data, indexes, triggers, constraints and permission specifications for that table.

Output: There is no Output because Book table will be deleted from database

SQL Difference Betweeen DELETE , DROP AND TRUNCATE Statement

In this part we will discuss the distingusih in delete,drop and truncate statement:-