TRUNCATE TABLE vs. DELETE vs. DROP TABLE: Eliminare tabelle e dati in SQL

Qual è la differenza tra troncare (TRUNCATE), cancellare (DELETE) e abbandonare (DROP) una tabella in SQL? Scopritelo in questo articolo.

Esistono diversi modi per eliminare i dati in SQL, tra cui i comandi DELETE, TRUNCATE TABLE e DROP TABLE. Quale usare in base alla situazione?

In questo articolo imparerete la sintassi di ciascun comando in diversi motori di database come MySQL, PostgreSQL, SQL Server e Oracle. Capirete inoltre la differenza tra DROP TABLE, DELETE e TRUNCATE TABLE.

Se volete esercitarvi con SQL, vi consiglio la nostra piattaforma di apprendimento SQL. LearnSQL.it offre oltre 50 corsi interattivi di SQL a vari livelli di competenza. Ad esempio, provate il nostro SQL Practice con oltre 600 esercizi pratici di SQL per esercitarsi.

Iniziamo!

DELETE

DELETE è un comando DML (Data Manipulation Language). Questo comando rimuove i dati da una tabella. Si usa solo per eliminare i dati da una tabella, non per rimuovere la tabella dal database.

Con questa sintassi è possibile eliminare tutti i dati:

DELETE FROM name_table;

Oppure si può eliminare un gruppo di dati utilizzando la clausola WHERE:

DELETE FROM name_table WHERE col=value;

Se volete rimuovere tutti i dati da una determinata tabella, utilizzate DELETE FROM seguito dal nome della tabella. Si noti che in questa sintassi non ci sono nomi di colonne; saranno perciò rimossi tutti i dati, non i dati delle colonne selezionate.

Se volete rimuovere dati specifici, usate WHERE con condizioni di filtraggio, come nel secondo esempio.

Utilizziamo questi comandi in un esempio. Ecco la tabella product:

Questa query ...

DELETE FROM product;

... rimuove tutti i dati della tabella product. Dopo questa query, la tabella product sarà vuota.

Ma la query WHERE...

DELETE FROM product WHERE price<2.90;

... elimina solo i dati del latte e dello zucchero, perché i loro prezzi sono inferiori a 2,90 dollari. (Il latte costa 2,40 dollari e lo zucchero 2,88 dollari).

Ora la tabella product contiene solo dati i cui prezzi sono superiori a 2,90 dollari:

Come funziona la funzione DELETE?

Se non si vuole rimuovere la struttura della tabella o se si vogliono eliminare solo delle righe specifiche, si usa il comando DELETE. Può rimuovere una, alcune o tutte le righe di una tabella. DELETE restituisce il numero di righe rimosse dalla tabella.

Tuttavia, DELETE utilizza un blocco di riga durante l'esecuzione e può essere annullato. Ogni riga cancellata è bloccata, quindi può produrre molti blocchi se si lavora su una tabella di grandi dimensioni.

DELETE mantiene anche l' incremento automatico dell'ID della tabella. Se si rimuove l'ultimo dato della tabella con ID=20 e poi si aggiunge un nuovo dato, questo avrà ID=21, anche se il dato immediatamente precedente ha ID=19.

DELETE può essere eseguita da trigger. Un trigger può essere richiamato prima, dopo o al posto dell'operazione DELETE. Può essere eseguito per qualsiasi cambiamento di riga o quando tutte le righe vengono rimosse. Anche la rimozione di righe in un'altra tabella può attivare DELETE.

Naturalmente, per utilizzare il comando DELETE è necessario disporre dei permessi DELETE per quella tabella.

TRUNCATE TABLE

TRUNCATE TABLE è simile a DELETE, ma questa operazione è un comando DDL (Data Definition Language). Anch'essa elimina i dati da una tabella senza rimuoverne la struttura, ma non utilizza la clausola WHERE. Ecco la sintassi:

TRUNCATE TABLE table_name;

Se si utilizza questo comando, tutte le righe di questa tabella verranno rimosse. La seguente query ...

TRUNCATE TABLE product;

... cancella tutti i dati memorizzati nella tabella product.

Come funziona TRUNCATE TABLE?

Fate attenzione quando usate questo comando. Le transazioni di TRUNCATE possono essere annullate in motori di database come SQL Server e PostgreSQL, ma non in MySQL e Oracle.

TRUNCATE è più veloce da usare rispetto a DELETE poiché non scansiona ogni dato prima di rimuoverlo. TRUNCATE TABLE blocca l'intera tabella per rimuoverne i dati; pertanto, questo comando utilizza anche meno spazio di transazione rispetto a DELETE.

A differenza di DELETE, TRUNCATE non restituisce il numero di righe eliminate dalla tabella. Inoltre, ripristina il valore di auto incremento della tabella al valore iniziale (di solito 1). Se si aggiunge un dato dopo aver troncato la tabella, esso avrà ID=1. Nota: in PostgreSQL, è possibile scegliere se riavviare o continuare il valore di auto incremento. Mentre Oracle utilizza una sequenza per incrementare i valori che non viene ripristinata da TRUNCATE.

Naturalmente, è necessario avere l'autorizzazione per utilizzare TRUNCATE TABLE. In PostgreSQL, è necessario il permesso TRUNCATE; in SQL Server, il permesso minimo è ALTER table; in MySQL, è necessario il permesso DROP. Infine, Oracle richiede il permesso di sistema DROP ANY TABLE per utilizzare questo comando.

Per saperne di più, potete consultare il corso "Le basi della creazione di tabelle in SQL", che fa parte del nostro Percorso di ingegneria dei dati.

DROP TABLE

DROP TABLE è un'altra operazione DDL (Data Definition Language). Ma non è usata per rimuovere semplicemente i dati da una tabella; questa operazione elimina la struttura della tabella dal database, insieme a tutti i dati memorizzati nella tabella.

Ecco la sintassi di questo comando:

DROP TABLE table_name;

Tutto ciò che serve dopo DROP TABLE è il nome della tabella che si desidera eliminare. Ad esempio, se si desidera rimuovere l'intera tabella product dal database, si scriverà:

DROP TABLE product;

Questo rimuove tutti i dati della tabella product e la struttura della tabella.

Come funziona DROP TABLE?

L'operazione DROP TABLE rimuove la definizione della tabella e i dati, nonché gli indici, i vincoli e i trigger relativi alla tabella.

Questo comando libera spazio nella memoria.

Durante l'esecuzione di DROP TABLE non vengono attivati trigger.

Questa operazione non può essere annullata in MySQL, ma può essere annullata in Oracle, SQL Server e PostgreSQL.

In SQL Server, per usare DROP TABLE è richiesto il permesso ALTER nello schema a cui appartiene la tabella; MySQL richiede il permesso DROP; Oracle richiede il permesso DROP ANY TABLE. In PostgreSQL, gli utenti possono eliminare le proprie tabelle.

DROP TABLE vs. DELETE TABLE vs. TRUNCATE TABLE in SQL

Quali casi richiedono l'uso di DROP TABLE? Quando è necessario utilizzare TRUNCATE o optare per un semplice DELETE? Abbiamo preparato la seguente tabella per riassumere le proprietà di ciascun comando:

Quale operazione è migliore in base al caso d'uso?

• Per rimuovere righe specifiche, utilizzate DELETE.
• Per rimuovere tutte le righe da una tabella di grandi dimensioni e lasciare la struttura della tabella, utilizzate TRUNCATE TABLE. È più veloce di DELETE.
• Per rimuovere un'intera tabella, compresi la struttura e i dati, utilizzate DROP TABLE.

Per saperne di più sulla creazione delle strutture dei database, consultate il nostro Percorso di ingegneria dei dati.

In SQL, DELETE, TRUNCATE TABLE e DELETE TABLE hanno tutte i loro vantaggi e svantaggi. Speriamo che questo articolo vi abbia aiutato a capire quando e come utilizzare ciascun comando.