SQL DELETE Table

SQL DELETE TABLE statement is used to delete rows from an existing table. 

If you want to delete some specific row(s) from a table, you can use WHERE condition along with the SQL DELETE Table statement.

SQL DELETE TABLE Syntax

The basic syntax of SQL DELETE TABLE is as below.

DELETE FROM table_name [WHERE condition];

HERE,

• DELETE: Keyword to delete the table.
• table_name: Specify the name of the table which needs to be deleted.
• [WHERE condition]: Optional field. If you need to delete some specific row(s), you can specify here the condition.

SQL DELETE TABLE Example

The below examples clarify the SQL DELETE Table statement.

DELETE Entire Table

Suppose, you have a STUDENTS table in the database with 4 rows in it and you want to delete the entire table i.e. all the row, then you need to follow below command.

DELETE FROM STUDENTS;

Now all the rows of the STUDENTS table have been deleted. You can verify by issuing a select statement on that table.

mysql> SELECT * FROM STUDENTS;
Empty set (0.00 sec)

SQL DELETE TABEL with WHERE

Suppose, we have to delete those students whose age are below 10. Then we can use the below statement.

DELETE FROM STUDENTS WHERE Age<10;

Now you can verify if only the specific row(s) has been deleted or not.

mysql> SELECT * FROM STUDENTS;
+--------+-----------+----------+------+
| RollNo | FirstName | LastName | Age  |
+--------+-----------+----------+------+
|     11 | Ranjit    | Bera     |   14 |
|     14 | Nick      | Jonas    |   12 |
+--------+-----------+----------+------+
2 rows in set (0.00 sec)

You may think that SQL DELETE Table, DROP TABLE, and TRUNCATE Table all are the same. But there are some differences between them.

Difference b/w DELETE Table and TRUNCATE Table

In the case of DELETE Table, only the rows from the table based on the condition defined by where clause or delete all the rows from the table when the condition is not specified. 

But it does free the space containing by the table.

But in the case of TRUNCATE statement, it is used to delete all the rows from the table and free the containing space.

If you want to delete the STUDENTS table which has been shown earlier using TRUNCATE statement, follow the below command.

TRUNCATE TABLE STUDENTS;

Difference b/w DROP and TRUNCTION statement

DROP statement deletes the complete rows along with the definition of a table, as a result all the relations between the table and other tables will no longer valid. Whenever you drop a table, the below will be drooped.

• The table structure.
• Relationships between tables.
• All integrity constants.
• All access privileges.

In the case of TRUNCATE table, the table structure will be the same, so you will not face any problems.