В чем разница DELETE и TRUNCATE в SQL?

Что проверяют

Понимаете ли вы, что это операции разного класса, и знаете ли, что можно откатить, а что нет.

Как отвечать

DELETE это обычная операция изменения данных: удаляет строки по условию, каждая удалённая строка попадает в журнал, срабатывают триггеры, действуют внешние ключи и правила доступа на уровне строк. Работает внутри транзакции и откатывается.

TRUNCATE это операция уровня схемы: очищает таблицу целиком, не проходя по строкам. Работает почти мгновенно независимо от объёма, потому что просто освобождает файлы данных.

Отличия по пунктам:

• условие: у DELETE есть WHERE, у TRUNCATE нет, он удаляет всё;
• скорость: DELETE пропорционален числу строк, TRUNCATE практически постоянен;
• триггеры: у DELETE срабатывают построчные, у TRUNCATE нет;
• блокировка: TRUNCATE берёт исключительную блокировку на таблицу, то есть на время операции с ней нельзя работать вовсе;
• счётчики и место: TRUNCATE умеет сбрасывать последовательности (RESTART IDENTITY) и сразу возвращает место, а после DELETE строки остаются мёртвыми до уборки;
• внешние ключи: TRUNCATE не выполнится, если на таблицу ссылаются, без указания CASCADE.

Важное уточнение про откат: в PostgreSQL TRUNCATE транзакционен и откатывается, а вот в MySQL и Oracle он выполняет неявную фиксацию и отката не имеет. Это частая причина неверного ответа.

Есть и третья операция: DROP TABLE удаляет саму таблицу вместе со структурой.

Где спотыкаются

Утверждают, что TRUNCATE нельзя откатить нигде.

Ждут срабатывания триггеров после TRUNCATE.

Используют DELETE без WHERE для очистки большой таблицы и надолго её блокируют.

Не знают про сброс последовательности и удивляются, что идентификаторы продолжаются.