TRUNCATE TABLE vs. DELETE vs. DROP TABLE: Eliminación de tablas y datos en SQL

¿Cuál es la diferencia entre truncar, borrar y eliminar una tabla en SQL? Descúbrelo en este artículo.

Hay muchas formas de eliminar datos en SQL, incluyendo los comandos DELETE, TRUNCATE TABLE y DROP TABLE. ¿Cómo saber cuál usar en una situación determinada?

En este artículo, aprenderás la sintaxis de cada comando en diferentes motores de bases de datos como MySQL, PostgreSQL, SQL Server y Oracle. Y entenderás el debate DROP TABLE vs. DELETE vs. TRUNCATE TABLE.

Si quieres practicar tus conocimientos de SQL, te recomiendo nuestra plataforma de aprendizaje de SQL LearnSQL.es. LearnSQL.es ofrece más de 50 cursos interactivos de SQL en varios niveles de competencia. Por ejemplo, prueba nuestro curso Ejercicio de SQL con más de 600 ejercicios prácticos de SQL para practicar tus conocimientos de SQL.

Ahora sí, ¡comencemos!

DELETE

DELETE es un comando DML (Lenguaje de Manipulación de Datos). Este comando elimina registros de una tabla. Se utiliza sólo para eliminar datos de una tabla, no para eliminar la tabla de la base de datos.

Puedes eliminar todos los registros con la sintaxis:

DELETE FROM name_table;

O puedes eliminar un grupo de registros utilizando la cláusula WHERE:

DELETE FROM name_table WHERE col=value;

Si quieres eliminar todos los registros de una tabla determinada, utiliza DELETE FROM seguido del nombre de la tabla. Observa que no hay nombres de columnas en esta sintaxis; se están eliminando todos los registros, no los datos de las columnas seleccionadas.

Si quieres eliminar registros específicos, utiliza WHERE con condiciones de filtrado, como en el segundo ejemplo.

Utilicemos estos comandos en un ejemplo. Esta es la tabla product:

Esta consulta...

DELETE FROM product;

... elimina todos los datos de la tabla product. Después de esta consulta, la tabla product estará vacía.

Pero la consulta WHERE...

DELETE FROM product WHERE price<2.90;

... elimina sólo los registros de milk (leche) y sugar (azúcar), porque sus precios son inferiores a $2.90 (la leche cuesta $2,40 y el azúcar 2,88 $).

Ahora, la tabla product sólo tiene registros con precios superiores a $2,90:

¿Cómo funciona DELETE?

Si no quieres eliminar la estructura de la tabla, o sólo quieres eliminar filas específicas, utiliza el comando DELETE. Puedes eliminar una, algunas o todas las filas de una tabla. DELETE devuelve el número de filas eliminadas de la tabla.

Sin embargo, DELETE utiliza un bloqueo de filas durante la ejecución y puede revertirse. Cada fila eliminada se bloquea, por lo que requerirá muchos bloqueos si se trabaja en una tabla grande.

DELETE también mantiene el ID de autoincremento en la tabla. Si eliminas el último registro de la tabla con ID=20 y luego añades un nuevo registro, este registro será ID=21 (aunque el registro inmediatamente anterior sea ID=19).

DELETE pueden ser ejecutado por disparadores (triggers). Un disparador puede ser llamado antes, después o en lugar de la operación DELETE. Se puede ejecutar para cualquier cambio de fila o cuando se eliminan todas las filas. La eliminación de filas en otra tabla también puede disparar DELETE.

Por supuesto, para utilizar el comando DELETE se necesita el permiso DELETE para esa tabla.

TRUNCATE TABLE

TRUNCATE TABLE es similar a DELETE, pero esta operación es un comando DDL (Lenguaje de Definición de Datos). También borra registros de una tabla sin eliminar la estructura de la misma, pero no utiliza la cláusula WHERE. Esta es la sintaxis:

TRUNCATE TABLE table_name;

Al utilizar este comando, eliminarás todas las filas de esta tabla. La siguiente consulta...

TRUNCATE TABLE product;

... borra todos los registros almacenados en la tabla product.

¿Cómo funciona TRUNCATE TABLE?

Ten cuidado al usar este comando. Las transacciones de TRUNCATE pueden revertirse en motores de bases de datos como SQL Server y PostgreSQL, pero no en MySQL y Oracle.

TRUNCATE es más rápido que DELETE, ya que no explora cada registro antes de eliminarlo. TRUNCATE TABLE bloquea toda la tabla para eliminar datos de una tabla; por lo tanto, este comando también utiliza menos espacio de transacción que DELETE.

A diferencia de DELETE, TRUNCATE no devuelve el número de filas eliminadas de la tabla. También restablece el valor de autoincremento de la tabla al valor inicial (normalmente 1). Si se añade un registro después de truncar la tabla, será ID=1. Nota: En PostgreSQL, se puede elegir reiniciar o continuar el valor de autoincremento. Oracle utiliza una secuencia para incrementar los valores, la cual no es reiniciada por TRUNCATE.

Por supuesto, necesita permiso para usar TRUNCATE TABLE. En PostgreSQL, se necesita el privilegio TRUNCATE; en SQL Server, el permiso mínimo es ALTER table; en MySQL, se necesita el privilegio DROP. Por último, Oracle requiere el privilegio de sistema DROP ANY TABLE para utilizar este comando.

Puedes aprender más en el curso "Fundamentos de la creación de tablas en SQL", que forma parte de nuestra Ruta de Ingeniería de Datos.

DROP TABLE

DROP TABLE es otra operación DDL (Lenguaje de Definición de Datos), pero no se utiliza para eliminar simplemente los datos de una tabla, sino que elimina la estructura de la tabla de la base de datos, junto con cualquier dato almacenado en la tabla.

Esta es la sintaxis de este comando:

DROP TABLE table_name;

Todo lo que se necesita después de DROP TABLE es el nombre de la tabla que se quiere eliminar. Por ejemplo, si quisieras eliminar toda la tabla product de la base de datos, deberías escribir:

DROP TABLE product;

Esto elimina todos los datos de la tabla product y la estructura de la tabla.

¿Cómo funciona DROP TABLE?

La operación DROP TABLE elimina la definición y los datos de la tabla, así como los índices, las restricciones y los disparadores relacionados con la tabla.

Este comando libera el espacio de memoria.

Cuando se ejecuta DROP TABLE no se activa ningún disparador.

Esta operación no se puede revertir en MySQL, pero sí en Oracle, SQL Server y PostgreSQL.

En SQL Server, DROP TABLE requiere el permiso ALTER en el esquema al que pertenece la tabla; MySQL requiere el privilegio DROP; Oracle requiere el privilegio DROP ANY TABLE. En PostgreSQL, los usuarios pueden eliminar sus propias tablas.

DROP TABLE vs. DELETE TABLE vs. TRUNCATE TABLE en SQL

¿Qué casos requieren DROP TABLE? ¿Cuándo se debe usar TRUNCATE u optar por un simple DELETE? Hemos preparado la siguiente tabla para resumir las propiedades de cada comando:

¿Qué operación es mejor para cada caso de uso?

• Para eliminar filas específicas, utiliza DELETE.
• Para eliminar todas las filas de una tabla grande y dejar la estructura de la tabla, utiliza TRUNCATE TABLE. Es más rápido que DELETE.
• Para eliminar una tabla entera, incluyendo su estructura y sus datos, utiliza DROP TABLE.

Puedes aprender más sobre la creación de estructuras de bases de datos en nuestra Ruta de Ingeniería de Datos.

En SQL, DELETE, TRUNCATE TABLE, y DELETE TABLE tienen sus ventajas y desventajas. Esperamos que este artículo te haya ayudado a entender cuándo y cómo usar cada comando.