Skip to content
Abode QA
Abode QA

A Hub For Testing Minds…

  • Home
  • Video Tutorial
    • Selenium WebDriver
    • Spock Framework
    • Katalon Studio
    • Git Tutorial
    • SQL Tutorial
    • Geb & Spock
    • Groovy
  • Manual Testing
  • Selenium-WebDriver
  • Quiz
    • Selenium WebDriver Quiz
    • Software Testing Quiz | Software Testing Interview Preparation Question
  • Java Tutorial
  • Katalon
  • Fitnesse
  • Daily Bytes!!
Abode QA

A Hub For Testing Minds…

a bucket of beer

Delete, Truncate and Drop Statement In SQL with Example

Dwarika Dhish Mishra, January 30, 2023August 31, 2023

When we need to manipulate data in a table stored in a database, we can use select, update, delete, truncate, and drop statements.

So in today’s post, we’ll go over the Delete, Truncate, and Drop statements in SQL and try to understand the differences between them.

Table Of Contents
  1. DELETE
  2. TRUNCATE
  3. DROP

DELETE

Delete statement is used to remove data on the basis of condition applied in where clause. So lets see the syntax of Delete Statement

DROP FROM table name WHERE clause;

For instance, we want to remove all data from workers whose age is less than 30, In that case we would write the sql query like this.

DELETE FROM workers WHERE age < 30;

So if we see above SQL query, then we can clearly see that delete statement will remove one or more rows which are imbibing the condition provided in where clause and remaining data remain intact which are not fulfilling the condition.

Trending
How to Run Selenium Scripts In Tor Browser?

Now we need to remember that delete statement only deletes the data, it is not going make any changes in table structure and indexes created for indexing. Since it is DML statement and it generates change logs as well so if necessary at any point of time, changes can be reverted using rollback statement. But always remember that commit statement has not be executed and running session has not been broken.

TRUNCATE

The TRUNCATE statement is used to remove all the data from a table. Unlike the DELETE statement, TRUNCATE does not have a WHERE clause, and it removes all the data from the table in a single operation.

TRUNCATE TABLE table_name;

For instance, we want to delete all rows from table workers, then here is the sql query

TRUNCATE TABLE workers;

Do you know that truncate statement is much more faster than Delete Statement, So lets list some of the points.

  • It does not create logs like Delete statement for all the changes made in tables.
  • it does not check rows because truncate query doesn’t have where clause. So no tension of rows count or its stats.
  • Truncate statement deallocate all the data pages associated with the tables, it means it clear all the data occupied on the disk and reset the structure of table back to its original state. It never traverse in rows of the tables it directly delete the data on disk and so it is much more faster than Delete statement.

It’s vital to remember that, unlike the DELETE statement, the TRUNCATE statement uses the Data Definition Language (DDL), not the Data Manipulation Language. This indicates that TRUNCATE is irreversible and cannot be undone. TRUNCATE also returns the table’s auto-increment value to 0 (zero).

DROP

Drop statement in SQL is used to delete the entire table from databases and it can’t be recovered because structure and indexing of entire table is removed from the disk. The DROP statement has the following syntax:

DROP TABLE table_name;

For example, we want to delete entire table staff from database we will write following query

Drop table staff;

So let’s list some of the important point regarding Drop statement.

  • Drop statement is DDL (Data Definition Language) So it is irreversible, it this statement by mistake you have executes on database sql client means table is gone forever.
  • Drop statement always remove data, indexes and constrains associated with tables.

In conclusion, We can say that Delete, Truncate and Drop Statement has its own use case and we should always be careful while using these statement and we should always be clear with the task to achieve.

Hope you have like this post.. Will meet again with next post.

Popular Posts

  • Top Mozilla Firefox Plugins For Better Productivity With Key Features!!
  • How To Run Selenium Tests In Brave Browser Using ChromeDriver?
  • Top Chrome Extensions For Better Productivity !!
  • Top Best Software Testing Trends to watch out in 2021

Share this:

  • Tweet
  • More
  • Pocket
  • Email
  • Reddit
  • Share on Tumblr
  • Print
  • WhatsApp

Related

SQL And Database

Post navigation

Previous post
Next post

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,582 other subscribers
  • Top-Notch Mozilla Firefox Add-ons to Install
  • 10 Best FREE YouTube Video Downloader Apps For Year 2021.
  • WebDriverManager: New Way to handle driver binaries in Selenium
  • Requirement of Testing environment
  • How To Create S3 Bucket Command? | AWS CLI Commands
  • How To Use LS and CP command In AWS?

Top Posts & Pages

  • Reviews,Walkthrough And Inspection In Software Testing
    Reviews,Walkthrough And Inspection In Software Testing
  • How to Connect Selenium To Existing Chrome Browser?
    How to Connect Selenium To Existing Chrome Browser?
  • How to use Contains() and starts-with() function in Xpath
    How to use Contains() and starts-with() function in Xpath
  • 5 Commonly Asked Java String Question in Selenium Interview
    5 Commonly Asked Java String Question in Selenium Interview
  • Upload and Download file from FTP Server using Java FTP Client
    Upload and Download file from FTP Server using Java FTP Client
  • Arrays in Java and its implementation in WebDriver
    Arrays in Java and its implementation in WebDriver
  • SSH To Ubuntu Installed On VirtualBox | Putty To Ubuntu
    SSH To Ubuntu Installed On VirtualBox | Putty To Ubuntu
  • "You are using an unsupported command-line flag --ignore-certificate-errors. Stability and security will suffer." Handling in WebDriver using ChromeOptions()
    "You are using an unsupported command-line flag --ignore-certificate-errors. Stability and security will suffer." Handling in WebDriver using ChromeOptions()
  • assertTrue(message,condition) in Selenium WebDriver and it's implementation
    assertTrue(message,condition) in Selenium WebDriver and it's implementation
  • Test Cases To Test Software Updates Before Its Release
    Test Cases To Test Software Updates Before Its Release
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
©2023 Abode QA | WordPress Theme by SuperbThemes