DELETE versus TRUNCATE - and a summary

You have already learned the scribe's three great commands. Finally, let us compare

DELETE

with its faster cousin -

TRUNCATE

.

DELETE - a precise scalpel

DELETE

removes rows selectively, according to

WHERE

. It can remove one row, several, or (without

WHERE

) all of them - removing them one by one:

1DELETE FROM wypozyczenia WHERE id = 3;

TRUNCATE - clearing the whole shelf

TRUNCATE

empties the entire table in one move - it is like sweeping all the scrolls off a shelf at once:

1TRUNCATE TABLE wypozyczenia;

Differences compared to

DELETE

:

• Clears the whole table - it does not accept

  WHERE

  .
• Is faster for large tables - it does not remove row by row.
• Resets AUTO_INCREMENT - the

  id

  counter returns to 1, as if the shelf were new.

| Trait | DELETE | TRUNCATE | |-------|--------|----------| | Row selection (WHERE) | yes | no | | Speed on large tables | slower | faster | | Reset AUTO_INCREMENT | no | yes |

A short reminder

DELETE

is a scalpel,

TRUNCATE

is a broom. Both empty shelves, but

TRUNCATE

does it in one move and starts the

id

numbering anew. This is a first step - in later locations we will return to this comparison more than once.