SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

Drop vs Truncate in SQL

In this article, we will learn and understand the Drop and Truncate commands and the difference between these two commands.

What is Drop Command?

Drop is a Data Definition Language command in the Structured Query Language. The Drop command in SQL is used to delete or drop the table definition, the database, indexes, view, triggers, and data constraints from the database tables.

 In the Structured Query Language, the drop command is used to drop the elements from the relational database management system (RDBMS).

The Delete command can roll back data once it is performed, but the Drop commands cannot roll back data once it is performed. The Drop command is used to free memory where table space is stored because drop commands delete the table and its contents permanently.

The Drop command is fast as compared to the truncate command in the Structured Query Language. But, the execution timing of the drop command is slow because it has many complications. 

To delete one or more columns from the table, we use the drop command with the ALTER TABLE command.

The syntax for DROP command to remove the database is as follow:

DROP DATABASE Database_Name;

In the above syntax, we have to specify the database name that we want to delete from the system.

Note: - Be careful before using the DROP command for deleting the database because the drop commands remove all the objects from the database like tables, indexes, views, triggers created in the database.

The syntax for removing the table is as follow:

DROP TABLE Table_Name;

In the above syntax, we have to specify the table name that we want to delete from the database.

The syntax for removing the multiple tables from the same database in a single statement is as follow:

DROP TABLE table1, table2, table3;

We can delete multiple tables from the same database using the above syntax.

The syntax for removing the index is as follow:

DROP INDEX Index_Name;

We have to specify the index name in the above syntax just after the Index Keyword.

The syntax for removing the view is as follow:

DROP VIEW View_Name;

We have to specify the view name in the above syntax just after the View Keyword.

The syntax for removing the constraint from the table using the ALTER TABLE is as follow:

ALTER TABLE Table_Name DROP CONSTRAINT Constraint_Name;

Using the ALTER TABLE command in the SQL, we can delete the constraint from the table.

Example of DROP command:

We will take a few examples using the DROP command.

Step 1: Create a database or use an existing database. We will use two databases for the DROP command.

Step 2: Create a new table or use an existing table, index, view.

Example 1: Write a query to drop table students_information from the school database.

We will first select the School database using the USE Keyword followed by the database name.

USE School;

We will write the drop command to delete the students_information table from the selected database.

Consider the table Students_information with the following records:

Student_IdStudent_NameStudent_CourseStudent_Marks
1AnjaliBCOM85
2PranavBCA80
3YogeshB.E88
4BhushanMBA95
5PoonamMCOM97
6BhaveshB.E90
7KhushiBSC94
8PiyushBCOM75
9NikitaBA88
10AishwariyaBSC70
DROP TABLE Students_Information;

We have dropped the Students_information table with all the records present inside the table from the above query.

To cross check whether the query was executed successfully or not, we will execute the SELECT query.

SELECT * FROM Students_Information;
Drop Vs Truncate In SQL

As we execute the select query on the Students_information table, the table doesn't exist message is displayed, which means the Drop command query on the Students_information table is executed successfully.

Example 2: Write a query to drop manager_view1 view from the company database.

Consider the manager_view1 view with the following records:

MANAGERIDMANAGER_NAMEMANAGER_DEPARTMENT
1Snehdeep KaurORACLE
3Abhishek ManishJAVA

We will write the drop command to delete the manager_view1 view from the selected database.

DROP VIEW Manager_view1;

We have dropped the manager_view1 view from the above query with all the records present inside the view.

To cross_check whether the query was executed successfully or not, we will execute the SELECT query.

SELECT * FROM Manager_view1;
Drop Vs Truncate In SQL

As we execute the select query on the manager_view1 view, the view doesn't exist message is displayed, which means the Drop command query on the manager_view1 view is executed successfully.

Example 3: Write a query to drop the Cricket database from the system.

Before executing the drop command for the database, we first execute the show databases command to verify at the end of the drop command that we successfully dropped the Cricket database from the system.

SHOW DATABASES;
Database
Chk
Company
Cricket
Employee
Information_schema
Mysql
Performance_schema
Phpmyadmin
School
Test
WordPress
Drop Vs Truncate In SQL

Above are the databases that already exist in the system; we will drop the Cricket database from the system. Dropping the database means deleting all the objects present inside the database table, view, index, triggers, etc.

Now, we will execute a query for dropping the Cricket database.

DROP DATABASE Cricket;

We have executed the drop query on the Cricket database. To check if the database is dropped successfully or not, we will execute the SHOW DATABASES query.

SHOW DATABASES;
Database
Chk
Company
Employee
Information_schema
Mysql
Performance_schema
Phpmyadmin
School
Test
WordPress
Drop Vs Truncate In SQL

As you can see, the cricket database is dropped from the system successfully.

Example 4: Write a query to delete the last name column from the employee table.

ALTER TABLE employee DROP Last_Name;

We have deleted the Last_Name column from the employee table using the DROP command with the ALTER command in the above query.

FieldTypeNullKeyDefaultExtra
EMPLOYEEIDint(11)NOPRINULL
FIRST_NAMEvarchar(20)YESNULL
SALARYint(11)YESNULL
CITYvarchar(20)YESNULL
DEPARTMENTvarchar(20)YESNULL
MANAGERIDint(11)YESNULL
Drop Vs Truncate In SQL

The above output shows that Last_Name is deleted from the employee table.

What is Truncate Command?

Truncate is another Data Definition Language command in the Structured Query Language. The Truncate command is used to delete all the records from the table. Like the DROP command, but the TRUNCATE command does not contain a WHERE clause. The Truncate command is faster than both the DROP and the DELETE command. We cannot roll back the records like the DROP command after using the TRUNCATE command.

Syntax of TRUNCATE command in SQL:

TRUNCATE TABLE tablename;

In the above syntax, we have to mention the table name whose data we want to delete from the table.

We will take a few examples using the TRUNCATE command.

Example of TRUNCATE command:

Step 1: Create a database or use an existing database.

Step 2: Create a new table or use an existing table.

Consider the existing table with the following records:

Table: Employee:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

Example: Write a query to delete all the records from the employee table using the truncate command.

TRUNCATE TABLE Employee;

We have deleted all the records from the above query from the employee table.

To cross check whether all the records from the employee table are deleted or not successfully, we will execute the SELECT query on the employee table.

SELECT * FROM Employee;
EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENT  MANAGERID
Drop Vs Truncate In SQL

As we execute the select query on the employee table, the empty set message is displayed, which means the truncate command on the employee table is executed successfully.

The below points show the difference between the Drop command and the Truncate command in the Structured Query Language:

The DROP CommandThe TRUNCATE Command
The DROP command deletes the table structure and table records.The TRUNCATE command drops all records from the table.
The DROP command is a Data Definition Language command.The TRUNCATE command is also a Data Definition Language command.
In the DROP command, table space is free from memoryThe TRUNCATE command does not free the table space from the memory.
The table view does not exist in the DROP commandThe table view exists in the TRUNCATE command
In the DROP command, we cannot use delete space.In the TRUNCATE command, we can use delete space but less than compared to the DELETE statement.
In the DROP command, the integrity constraints will be removed automatically from the table.  The integrity constraints will not be removed from the table in the TRUNCATE command.
The DROP command deletes the records fast, but there are many complications.The TRUNCATE command is faster than the DROP command.



ADVERTISEMENT
ADVERTISEMENT