Ответ

DELETE и TRUNCATE — оба инструмента для очистки таблиц, но работают по-разному:

DELETE удаляет строки по условию или все, поддерживает WHERE, можно откатить, активирует триггеры. Может быть медленным при большом числе строк (по одной строке).
TRUNCATE мгновенно очищает всю таблицу, нельзя использовать WHERE, часто не логгируется по-строчно, не всегда активирует триггеры. В большинстве случаев действие неотменяемо, нельзя откатить в отдельных СУБД.

Тонкости:

DELETE поддерживает откат транзакции (ROLLBACK), TRUNCATE — нет или зависит от СУБД.
TRUNCATE обычно сбрасывает identity/sequence счётчик.
TRUNCATE не применим, если есть внешние ключи или ссылки на таблицу.

Пример:

-- Удалить заказы старше 3 лет
DELETE FROM orders WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

-- Полностью очистить таблицу (быстро)
TRUNCATE TABLE logs;

Вопрос с подвохом

Можно ли восстановить данные после TRUNCATE?

Типичная ошибка: считают, что как и DELETE, TRUNCATE можно откатить транзакцией. Но TRUNCATE обычно нельзя откатить или сделать UNDO, кроме некоторых СУБД (например, PostgreSQL при работе внутри транзакции).

Пример (в большинстве СУБД):

BEGIN;
TRUNCATE TABLE orders;
ROLLBACK; -- В большинстве СУБД не сработает, данные потеряны!

Примеры реальных ошибок из-за незнания тонкостей темы

История

Удаление временных данных в продакшен-базе TRUNCATE'ом, вместо TEST. Триггер аудита по удалению строк сработал только на DELETE — информация о потерянных данных, времени и пользователе отсутствует. Пострадал разбор инцидента.

История

Очищение таблицы TRUNCATE при наличии внешнего ключа. SQL выбросил ошибку из-за ограничения ссылочной целостности, скрипт не завершился, автоматизация остановилась. Решение: временно убрать внешние ключи либо использовать DELETE.

История

Массовое удаление данных через DELETE в больших таблицах без батчинга: из-за долгих блокировок база "зависла", другие транзакции ожидали освобождения блокировок. Итог — простой системы. Корректно: использовать DELETE LIMIT/OFFSET, если поддерживается, или батчи.