Syntax of DELETE operator

From time to time the task of deleting records from the table arises. For this, SQL provides operators DELETE and TRUNCATE, of which the first option is the most universal and safe.

General structure of a query with an operator DELETE

MySQL
DELETE FROM table_name
[WHERE the_conditions_of_the_limitations];

If there is no WHERE record selection condition, then all records will be deleted the specified table.

The same operation (deleting all records) can also be done using TRUNCATE operator. It will drop the table and re-create it - this option is much faster, than deleting all records one by one (as is the case with DELETE) especially for large tables.

General structure of a query with the TRUNCATE statement

MySQL
TRUNCATE TABLE table_name;

The MySQL query optimizer automatically uses the TRUNCATE statement if the DELETE statement does not contain a WHERE clause or LIMIT constructs.

However, the TRUNCATE statement has several differences:

Triggers are not processed, in particular, the delete trigger
Deletes all rows in a table without writing the deletion of individual rows of data to the transaction log
Resets the counter of identifiers to the initial value
To use, you must have edit rights to the table

Deleting records for multi-table queries

If the DELETE request uses JOIN, then it is necessary to specify from which table (s) you want to delete records.

MySQL
DELETE table_name_1 [, table_name_2] FROM
table_name_1 JOIN table_name_2
ON table_name_1.field = table_name_2.field
[WHERE the_conditions_of_the_limitations];

For example, we need to delete all reservations for a home that does not have a kitchen. Then the request will look like this:

MySQL
DELETE Reservations FROM
Reservations JOIN Rooms ON
Reservations.room_id = Rooms.id
WHERE Rooms.has_kitchen = false;

If, in addition to deleting the reservation, we also needed to delete the accommodation, then the query would look like this:

MySQL
DELETE Reservations, Rooms FROM
Reservations JOIN Rooms ON
Reservations.room_id = Rooms.id
WHERE Rooms.has_kitchen = false;