TRUNCATE TABLE vs. DELETE vs. DROP TABLE: Excluindo Tabelas e Dados em SQL

Qual é a diferença entre TRUNCATE TABLE, DELETE e DROP TABLE em SQL? Descubra neste artigo.

Há muitas maneiras de excluir dados em SQL, incluindo os comandos DELETE, TRUNCATE TABLE e DROP TABLE. Qual deles você deve usar em cada situação?

Neste artigo, você aprenderá a sintaxe de cada comando em diferentes servidores de banco de dados, como MySQL, PostgreSQL, SQL Server e Oracle. Além disso, você entenderá a comparação entre DROP TABLE vs. DELETE vs. TRUNCATE TABLE.

Se você quiser praticar suas habilidades em SQL, recomendo nossa plataforma de aprendizado SQL LearnSQL.com.br. O LearnSQL.com.br oferece mais de 50 cursos interativos em SQL, para diferentes níveis de proficiência. Experimente por exemplo a nossa Trilha de Práticas em SQL, com mais de 600 exercícios práticos em SQL para exercitar suas habilidades.

Agora, vamos começar!

DELETE

DELETE é um comando DML (Data Manipulation Language), que remove registros de uma tabela. Ele é usado apenas para apagar dados de uma tabela, e não para remover a tabela do banco de dados.

Você pode excluir todos os registros com a sintaxe:

DELETE FROM nome_tabela;

Ou você pode excluir um grupo de registros usando a cláusula WHERE:

DELETE FROM nome_tabela WHERE col=value;

Se você quiser remover todos os registros de uma determinada tabela, use DELETE FROM seguido do nome da tabela. Note que não há nomes de colunas nesta sintaxe; você está removendo todos os registros, não os dados nas colunas selecionadas.

Se você quiser remover registros específicos, use WHERE com condições de filtragem, como no segundo exemplo.

Vamos ver o funcionamento desses comandos em um exemplo. A seguir, temos a tabela produto:

Esta consulta ...

DELETE FROM produto;

... remove todos os dados da tabela produto. Após esta consulta, a tabela produto estará vazia.

Mas a consulta WHERE...

DELETE FROM produto WHERE preco<2.90;

... elimina apenas os registros de leite e açúcar, pois seus preços são inferiores a R$ 2,90 (o leite custa R$ 2,40 e o açúcar R$ 2,88).

Agora, a tabela produto só tem registros com preços superiores a R$ 2,90:

Como funciona o comando DELETE?

Se você não quiser excluir a estrutura da tabela ou estiver excluindo apenas linhas específicas, use o comando DELETE. Ele pode excluir uma, algumas, ou todas as linhas de uma tabela. DELETE retorna o número de linhas removidas da tabela.

Entretanto, o comando DELETE usa um bloqueio de linha durante a execução, podendo ser revertido. Cada linha eliminada é bloqueada, portanto, se você estiver trabalhando com uma tabela grande, será necessário um grande número de bloqueios.

DELETE também mantém o auto-incremento do índice (ID) na tabela. Se você remover o último registro na tabela, com ID=20 e, depois, adicionar um novo registro, este registro terá ID=21 - mesmo que o registro imediatamente antes tenha ID=19.

DELETE pode ser executado por gatilhos (os chamados triggers). Um trigger pode ser chamado antes, depois ou no lugar da operação DELETE. Ele pode ser executado para qualquer mudança de linha ou quando todas as linhas forem removidas. A remoção de linhas em outra tabela também pode acionar o comando DELETE.

É claro, para usar o comando DELETE, você precisa ter uma permissão de DELETE para essa tabela.

TRUNCATE TABLE

TRUNCATE TABLE é semelhante a DELETE, mas esta operação é um comando DDL (Data Definition Language). Ele também elimina registros de uma tabela sem remover a estrutura da tabela, mas não usa a cláusula WHERE. Aqui está a sintaxe:

TRUNCATE TABLE nome_tabela;

Se você usar este comando, todas as linhas da tabela serão removidas. A seguinte consulta ...

TRUNCATE TABLE produto;

... apaga todos os registros armazenados na tabela produto.

Como funciona o comando TRUNCATE TABLE?

Tenha cuidado ao usar este comando. Operações TRUNCATE podem ser revertidas em servidores de banco de dados como SQL Server e PostgreSQL, mas não no MySQL e Oracle.

TRUNCATE é mais rápido que DELETE, pois não escaneia cada registro antes de removê-lo. TRUNCATE TABLE bloqueia a tabela inteira para remover dados dela; assim, este comando também utiliza menos espaço de operação se comparado ao comando DELETE.

Ao contrário de DELETE, TRUNCATE não retorna o número de linhas apagadas da tabela. Ele também redefine o auto-incremento de valores da tabela para o valor inicial (geralmente 1). Se você adicionar um registro após executar um comando TRUNCATE TABLE, ele terá ID=1. Nota: No PostgreSQL, você pode optar por reiniciar ou continuar o auto-incremento. O Oracle usa uma sequência para incrementar os valores, que não é reinicializada pelo comando TRUNCATE.

Você precisa de permissão para usar TRUNCATE TABLE. No PostgreSQL, você precisa da permissão TRUNCATE; no SQL Server, a permissão mínima é de ALTER; no MySQL, você precisa da permissão DROP. Finalmente, o Oracle requer a permissão DROP ANY TABLE para usar este comando.

Você pode aprender mais no curso "Noções Básicas sobre Criação de Tabelas em SQL", que faz parte da nossa Trilha de Engenharia de Dados.

DROP TABLE

O comando DROP TABLE é outra operação de DDL (Data Definition Language), mas não é usado apenas para remover dados de uma tabela; ele elimina a estrutura da tabela do banco de dados, juntamente com quaisquer dados armazenados nela.

Aqui está a sintaxe deste comando:

DROP TABLE nome_tabela;

Depois de DROP TABLE, você precisa incluir apenas o nome da tabela que deseja excluir. Por exemplo, se você desejasse excluir toda a tabela produto do banco de dados, você escreveria:

DROP TABLE produto;

Isto remove todos os dados da tabela produto, assim como a estrutura da tabela.

Como funciona o comando DROP TABLE?

A operação DROP TABLE remove a definição e os dados da tabela, assim como os índices, restrições e triggers relacionados a ela.

Este comando libera espaço na memória.

Nenhum trigger é disparado ao executar o comando DROP TABLE.

Esta operação não pode ser revertida no MySQL, mas pode ser revertida no Oracle, SQL Server e PostgreSQL.

No SQL Server, DROP TABLE requer a permissão ALTER no esquema ao qual a tabela pertence; MySQL requer a permissão DROP; Oracle requer a permissão DROP ANY TABLE. No PostgreSQL, os usuários têm liberdade para excluir suas próprias tabelas.

DROP TABLE vs. DELETE TABLE vs. TRUNCATE TABLE em SQL

Quais casos requerem DROP TABLE? Quando você deve usar TRUNCATE ou optar por um simples DELETE? Criamos a tabela abaixo para resumir as propriedades de cada comando:

Qual operação é melhor para cada caso?

• Para remover linhas específicas, use DELETE.
• Para remover todas as linhas de uma tabela grande e preservar a estrutura da tabela, use TRUNCATE TABLE. É mais rápido do que usar DELETE.
• Para remover uma tabela inteira, incluindo sua estrutura e seus dados, use DROP TABLE.

Aprenda mais sobre a criação de estruturas de banco de dados em nossa Trilha de Engenharia de Dados.

Em SQL, DELETE, TRUNCATE TABLE e DELETE TABLE têm vantagens e desvantagens. Esperamos que este artigo tenha ajudado você a entender quando e como usar cada comando.