SQL and MySQL course Β· Module 5: Modifying data (DML)

DELETE versus TRUNCATE - and a summary

2 min read Β·
In this lesson3

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:

DELETE 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:

TRUNCATE 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.
TraitDELETETRUNCATE
Row selection (WHERE)yesno
Speed on large tablesslowerfaster
Reset AUTO_INCREMENTnoyes

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.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What does the command TRUNCATE TABLE wypozyczenia; do?

  2. 2. How does TRUNCATE differ from DELETE without WHERE?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Click in order

    Click the elements in the order in which we build a command that clears the whole table:

  • Code editor

    Write a command that removes from the ksiazki table all books marked as unavailable (dostepna = 0). Use a WHERE clause.

  1. QuestionWhat is TRUNCATE
  2. QuestionDifference between DELETE and TRUNCATE
  3. Click in orderClearing the wypozyczenia table
  4. QuestionTRUNCATE and WHERE
  5. Coding taskDelete unavailable books

Useful articles