TRUNCATE vs. DELETE en SQL: Entendiendo las Diferencias

Aprenda cómo la TRUNCATE y BORRAR en SQL, las diferencias entre ellas y cuándo es preferible utilizar una en lugar de otra.

Al trabajar con tablas de bases de datos, es posible que tenga que eliminar un subconjunto de filas o todas las filas. Para eliminar filas de una tabla de base de datos, puede utilizar las sentencias SQL TRUNCATE o DELETE en función del caso de uso.

En este tutorial, veremos más de cerca cada una de las sentencias, comprenderemos cómo funcionan y decidiremos cuándo es preferible utilizar TRUNCATE en lugar de DELETE y viceversa.

Antes de continuar, es útil repasar los siguientes subconjuntos de SQL: 

• Lenguaje de definición de datos (DDL) se utilizan para crear y gestionar objetos de base de datos como tablas. Las sentencias SQL CREATE, DROPy TRUNCATE son ejemplos de sentencias DDL.
• Lenguaje de manipulación de datos (DML) se utilizan para manipular los datos de los objetos de la base de datos. Las sentencias DML se utilizan para realizar las operaciones de creación, lectura, actualización y eliminación de registros.
• Lenguaje de consulta de datos (DQL) se utilizan para recuperar datos de las tablas de la base de datos. Todas las SELECT pertenecen al subconjunto DQL.

Cómo utilizar la sentencia SQL TRUNCATE

Sintaxis de la sentencia SQL TRUNCATE

La sintaxis para utilizar la sentencia SQL TRUNCATE es la siguiente:

TRUNCATE TABLE table_name;

Ejecutando el comando TRUNCATE anterior se borra todos las filas de la tabla especificada por table_name-y no borra la tabla.

La operación de truncado no escanea todos los registros de la tabla. Por eso es razonablemente más rápida cuando se trabaja con tablas de bases de datos grandes.

Ejemplo de uso de SQL TRUNCATE

📑 Nota: Si tiene MySQL instalado en su máquina, puede codificar utilizando el cliente de línea de comandos MySQL. También puede seguirlo en otro DBMS de su elección como PostgreSQL.

Primero vamos a crear una base de datos con la que trabajar:

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (1.50 sec)

A continuación, seleccione la base de datos que acabamos de crear:

mysql> use db1;
Database changed

El siguiente paso es crear una tabla de base de datos. Ejecute la siguiente sentencia CREATE TABLE para crear una tabla sencilla tasks mesa:

-- Create the tasks table
CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    due_date DATE,
    status ENUM('Pending', 'In Progress', 'Completed') DEFAULT 'Pending',
    assignee VARCHAR(100)
);

En este ejemplo, el tasks tiene las siguientes columnas:

• task_id: Un identificador único que se incrementa automáticamente para cada tarea.
• title: El título o nombre de la tarea, limitado a 255 caracteres.
• due_date: La fecha de vencimiento de la tarea, representada como una fecha.
• status: El estado de la tarea, que puede ser "Pendiente", "En curso" o "Completada". El valor por defecto es "Pendiente".
• assignee: La persona asignada para la tarea concreta.

Ahora que hemos creado el tasks vamos a insertar registros en ella:

-- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ('Task 4', '2023-08-13', 'Pending', 'Alice'),
    ('Task 5', '2023-08-14', 'In Progress', 'Bob'),
    ('Task 6', '2023-08-15', 'Completed', 'Emily'),
    ('Task 7', '2023-08-16', 'Pending', 'David'),
    ('Task 8', '2023-08-17', 'In Progress', 'Olivia'),
    ('Task 9', '2023-08-18', 'Pending', 'Daniel'),
    ('Task 10', '2023-08-19', 'Completed', 'Sophia'),
    ('Task 11', '2023-08-20', 'Pending', 'Matthew'),
    ('Task 12', '2023-08-21', 'In Progress', 'Ava'),
    ('Task 13', '2023-08-22', 'Completed', 'William'),
    ('Task 14', '2023-08-23', 'Pending', 'Ella'),
    ('Task 15', '2023-08-24', 'In Progress', 'James'),
    ('Task 16', '2023-08-25', 'Completed', 'Lily'),
    ('Task 17', '2023-08-26', 'Pending', 'Benjamin'),
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

Al ejecutar la sentencia de inserción, debería ver una salida similar:

Query OK, 20 rows affected (0.18 sec)
Records: 20  Duplicates: 0  Warnings: 0

Ahora ejecute el comando TRUNCATE table para eliminar todos los registros de la tabla tasks mesa:

TRUNCATE TABLE tasks;
Query OK, 0 rows affected (0.72 sec)

Al hacerlo, se eliminan todos los registros y no la tabla. Puede comprobarlo ejecutando SHOW TABLES; así:

SHOW TABLES;

+---------------+
| Tables_in_db1 |
+---------------+
| tasks         |
+---------------+
1 row in set (0.00 sec)

Y una consulta SELECT para recuperar datos de la tabla de tareas devuelve un conjunto vacío:

SELECT * FROM tasks;
Empty set (0.00 sec)

Cómo utilizar la sentencia DELETE de SQL

Sintaxis de la sentencia SQL DELETE

La sintaxis general para utilizar la sentencia DELETE de SQL es la siguiente:

DELETE FROM table_name 
WHERE condition;

En condition en la cláusula WHERE es el predicado que determina que de las filas. La sentencia DELETE borra todas las filas para las que el predicado es True.

La sentencia DELETE, por tanto, permite tener un mayor control sobre qué registros se eliminan.

Pero, ¿qué ocurre cuando se utiliza la sentencia DELETE sin la cláusula WHERE?🤔

DELETE FROM table_name;

Ejecutando la sentencia DELETE como se muestra se elimina todos las filas de la tabla de la base de datos.

Si una sentencia DELETE o un conjunto de sentencias DELETE forman parte de una transacción no comprometida, puede deshacer los cambios. Sin embargo, se recomienda hacer una copia de seguridad de los datos en otro lugar.

Ejemplo de uso de SQL DELETE

Ahora veamos la sentencia SQL delete en acción.

Hemos borrado todos los registros del tasks tabla. Así que puede volver a ejecutar la sentencia INSERT (que ejecutamos anteriormente) para insertar registros:

-- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ...
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

Primero vamos a utilizar la sentencia DELETE con la cláusula WHERE. La siguiente consulta elimina todas las filas cuyo estado es "Completado":

DELETE FROM tasks WHERE status = 'Completed';
Query OK, 6 rows affected (0.14 sec)

Ahora ejecute esta consulta SELECT:

SELECT * FROM tasks;

Verás que actualmente hay 14 filas:

+---------+---------+------------+-------------+----------+
| task_id | title   | due_date   | status      | assignee |
+---------+---------+------------+-------------+----------+
|       1 | Task 1  | 2023-08-10 | Pending     | John     |
|       2 | Task 2  | 2023-08-11 | In Progress | Jane     |
|       4 | Task 4  | 2023-08-13 | Pending     | Alice    |
|       5 | Task 5  | 2023-08-14 | In Progress | Bob      |
|       7 | Task 7  | 2023-08-16 | Pending     | David    |
|       8 | Task 8  | 2023-08-17 | In Progress | Olivia   |
|       9 | Task 9  | 2023-08-18 | Pending     | Daniel   |
|      11 | Task 11 | 2023-08-20 | Pending     | Matthew  |
|      12 | Task 12 | 2023-08-21 | In Progress | Ava      |
|      14 | Task 14 | 2023-08-23 | Pending     | Ella     |
|      15 | Task 15 | 2023-08-24 | In Progress | James    |
|      17 | Task 17 | 2023-08-26 | Pending     | Benjamin |
|      18 | Task 18 | 2023-08-27 | In Progress | Mia      |
|      19 | Task 19 | 2023-08-28 | Pending     | Henry    |
+---------+---------+------------+-------------+----------+
14 rows in set (0.00 sec)

Ejecutando la siguiente sentencia DELETE se eliminan los 14 registros restantes de la tabla:

DELETE FROM tasks;
Query OK, 14 rows affected (0.20 sec)

Y la tabla de tareas está ahora vacía:

SELECT * FROM tasks;
Empty set (0.00 sec)

La sentencia SQL DROP

Hasta ahora, hemos aprendido:

• La sentencia TRUNCATE borra todas las filas de la tabla.
• La sentencia DELETE-sin cláusula WHERE-elimina todos los registros de la tabla.

Sin embargo, las sentencias TRUNCATE y DELETE no eliminan la tabla. Si desea eliminar la tabla de la base de datos, puede utilizar la sentencia DROP TABLE así:

DROP TABLE table_name;

Ahora vamos a eliminar la tabla de tareas de la base de datos:

mysql> DROP TABLE tasks;
Query OK, 0 rows affected (0.43 sec)

Verá que SHOW TABLES; devuelve un conjunto vacío (ya que hemos eliminado la única tabla que estaba presente en la base de datos):

mysql> SHOW TABLES;
Empty set (0.00 sec)

Cuándo usar TRUNCATE vs. DELETE en SQL

Resumiendo:

• Cuando necesite eliminar todas las filas de una tabla de base de datos de gran tamaño, utilice la sentencia TRUNCATE.
• Para eliminar un subconjunto de filas basándose en condiciones específicas, utilice la sentencia DELETE.

Resumen

Concluyamos nuestro debate con un resumen:

• Al trabajar con tablas de bases de datos, es posible que desee eliminar el subconjunto de filas o todas las filas de una tabla en particular. Para ello puede utilizar las sentencias TRUNCATE o DELETE.
• La sentencia TRUNCATE toma la sintaxis: TRUNCATE TABLE table_name;. Elimina todas las filas de la tabla especificada por table_name pero no elimina la tabla en sí.
• La sentencia DELETE tiene la sintaxis: DELETE FROM table_name WHERE condition;. Esto elimina las filas para las que el predicado condition es cierto.
• Ejecución de la sentencia SQL DELETE sin la cláusula WHERE borra todas las filas de la tabla. Funcionalmente, se obtiene el mismo resultado que con la sentencia TRUNCATE de SQL.
• Ejecutar TRUNCATE es especialmente rápido cuando se trabaja con tablas grandes, ya que no escanea toda la tabla. Así que cuando necesites borrar todas las filas de una tabla de base de datos grande, ejecutar truncar puede ser más eficiente.
• Si necesita eliminar un subconjunto de filas en función de una condición específica, puede utilizar la sentencia DELETE de SQL.

Si desea un repaso rápido de los comandos SQL más utilizados, consulte lo siguiente Hoja de trucos SQL.