TRUNCATE vs. DELETE in SQL: Understanding the Differences

by

Learn the way the ABBREVIATION And TO DELETE statements work in SQL, the variations between them and when you must use one over the opposite.

When working with database tables, it’s possible you’ll must delete a subset of rows or all rows. To delete rows from a database desk, you should utilize the SQL TRUNCATE or DELETE statements, relying on the utilization.

On this tutorial, we’ll take a better take a look at every of the statements, perceive how they work, and resolve when to make use of TRUNCATE over DELETE and vice versa.

Earlier than we go any additional, it is useful to evaluation the next SQL subsets:

  • Knowledge definition language (DDL) statements are used to create and handle database objects equivalent to tables. The SQL CREATE, DROPAnd TRUNCATE statements are examples of DDL statements.
  • Knowledge manipulation language (DML) statements are used to control information in database objects. DML statements are used to carry out the file creation, studying, updating, and deleting operations.
  • Knowledge question language (DQL) statements are used to retrieve information from database tables. All SELECT statements fall below the DQL subset.

Easy methods to use the SQL TRUNCATE assertion

truncate vs delete

Syntax of the SQL TRUNCATE assertion

The syntax for utilizing the SQL TRUNCATE assertion is as follows:

TRUNCATE TABLE table_name;

Operating the TRUNCATE command above will delete the file all the rows within the desk specified by table_name—and doesn’t delete the desk.

The truncation operation doesn’t scan all information within the desk. So it’s moderately quicker when working with massive database tables.

SQL TRUNCATE use case

📑 Comment: If MySQL is put in in your laptop, you possibly can code alongside utilizing the MySQL command line consumer. It’s also possible to comply with alongside in one other DBMS of your alternative, equivalent to PostgreSQL.

First, let’s create a database to work with:

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

Subsequent, choose the database we simply created:

mysql> use db1;
Database modified

The following step is to create a database desk. Run the next CREATE TABLE assertion to create a easy duties desk:

-- Create the duties desk
CREATE TABLE duties (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    due_date DATE,
    standing ENUM('Pending', 'In Progress', 'Accomplished') DEFAULT 'Pending',
    assignee VARCHAR(100)
);

On this instance, the duties desk has the next columns:

  • task_id: An auto-incrementing distinctive identifier for every job.
  • title: The title or identify of the job, restricted to 255 characters.
  • due_date: The due date for the duty, displayed as a date.
  • standing: The standing of the duty, which will be “Pending”, “In Progress”, or “Accomplished”. The default worth is about to ‘Pending’.
  • assignee: The individual answerable for the particular process.

Now we the duties desk, let’s insert information into it:

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

When executing the insert assertion you must see an analogous output:

Question OK, 20 rows affected (0.18 sec)
Data: 20  Duplicates: 0  Warnings: 0

Now run the TRUNCATE desk command to extract all information from the duties desk:

TRUNCATE TABLE duties;
Question OK, 0 rows affected (0.72 sec)

Doing this can delete all information and never the desk. You possibly can confirm this by working SHOW TABLES; like so:

SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| duties         |
+---------------+
1 row in set (0.00 sec)

And a SELECT question to retrieve information from the job desk returns an empty set:

SELECT * FROM duties;
Empty set (0.00 sec)

Easy methods to use the SQL DELETE assertion

A woman standing in front of a blackboard explaining the differences between truncate and delete SQL statements.

Syntax of the SQL DELETE assertion

The final syntax for utilizing the SQL DELETE assertion is as follows:

DELETE FROM table_name 
WHERE situation;

The situation in WHERE clause is the predicate that determines which of the rows must be eliminated. The DELETE assertion deletes all rows for which the predicate is True.

Due to this fact, the DELETE assertion provides you extra management over which information are deleted.

However what occurs for those who use the DELETE assertion with out the WHERE clause?

DELETE FROM table_name;

Executing the DELETE assertion as proven deletes all the rows within the database desk.

If a DELETE assertion or set of DELETE statements is a part of an uncommitted transaction, you possibly can undo the modifications. Nonetheless, it is suggested that you simply again up your information elsewhere.

SQL DELETE use case

Now let’s have a look at the SQL delete assertion in motion.

We now have all information from the duties desk. So you possibly can run the INSERT assertion (which we ran earlier) once more to insert information:

-- Inserting a number of information into the duties desk
INSERT INTO duties (title, due_date, standing, assignee)
VALUES
    ('Process 1', '2023-08-10', 'Pending', 'John'),
    ('Process 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Process 3', '2023-08-12', 'Accomplished', 'Mike'),
    ...
    ('Process 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Process 19', '2023-08-28', 'Pending', 'Henry'),
    ('Process 20', '2023-08-29', 'Accomplished', 'Isabella');

First, let’s use DELETE assertion with WHERE clause. The next question deletes all rows whose standing is “Full”:

DELETE FROM duties WHERE standing = 'Accomplished';
Question OK, 6 rows affected (0.14 sec)

Now run this SELECT question:

SELECT * FROM duties;

You will notice that there are presently 14 rows:

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

Executing the next DELETE assertion deletes all different 14 information within the desk:

DELETE FROM duties;
Question OK, 14 rows affected (0.20 sec)

And the duty desk is now empty:

SELECT * FROM duties;
Empty set (0.00 sec)

The SQL DROP assertion

To date we now have realized:

  • The TRUNCATE assertion removes all rows from the desk.
  • The DELETE assertion (with out a WHERE clause) deletes all information from the desk.

Nonetheless, the TRUNCATE and DELETE statements don’t delete the desk. If you wish to take away the desk from the database, you should utilize the DROP TABLE command as follows:

DROP TABLE table_name;

Now let’s delete the job desk from the database:

mysql> DROP TABLE duties;
Question OK, 0 rows affected (0.43 sec)

You will notice SHOW TABLES; returns an empty set (as a result of we deleted the one desk that existed within the database):

mysql> SHOW TABLES;
Empty set (0.00 sec)

When to make use of TRUNCATE vs DELETE in SQL?

Perform ABBREVIATION TO DELETE
Syntax TRUNCATE TABLE table_name; With WHERE clause: DELETE FROM table_name WHERE situation;
With out WHERE clause: DELETE TABLE table_name;
SQL subset Knowledge definition language (DDL) Knowledge manipulation language (DML)
Impact Deletes all rows within the database desk. When the DELETE assertion is executed with out the WHERE clause, all information within the database desk are deleted.
Efficiency Extra environment friendly than the DELETE assertion when working with massive tables. Much less environment friendly than the TRUNCATE assertion.

To sum up:

  • When you want to take away all rows from a big database desk, use the TRUNCATE assertion.
  • To delete a subset of the rows based mostly on particular circumstances, use the DELETE assertion.

Sum up

Let’s shut our dialogue with a abstract:

  • When working with database tables, it’s possible you’ll need to delete the subset of rows or all rows in a selected desk. You should utilize the TRUNCATE or DELETE statements for this.
  • The TRUNCATE assertion has the next syntax: TRUNCATE TABLE table_name;. It deletes all rows within the desk specified by table_name however doesn’t delete the desk itself.
  • The DELETE assertion has the next syntax: DELETE FROM table_name WHERE situation;. Removes the rows for which the predicate situation is true.
  • Executing the SQL DELETE assertion with out the WHERE clause deletes all rows within the desk. Functionally, this produces the identical outcome because the SQL TRUNCATE assertion.
  • Operating TRUNCATE is very quicker when working with bigger tables as a result of it doesn’t scan the complete desk. So if you want to delete all rows in a big database desk, working truncate will be extra environment friendly.
  • When you want to delete a subset of rows (based mostly on a particular situation), you should utilize the SQL DELETE assertion.

For a fast overview of generally used SQL instructions, try this SQL cheat sheet.

Leave a Comment

porno izle altyazılı porno porno