DROP command in SQL is a DDL command that permanently removes the data (or existing table) from the database and free the space from the memory. In this article, we will briefly discuss how to drop column, table, or database in SQL.
In the previous article, we have discussed how to remove the data from the existing table using SQL TRUNCATE and SQL DELETE command. Now in this article, we will again discuss how to remove the data or table from the database using DROP command.
So, what’s the difference between SQL TRUNCATE, SQL DELETE and SQL DROP and how SQL DROP works.
Let’s explore in this article.
Table of Content
- What is the DROP command in SQL?
- SQL DROP Column
- SQL DROP TABLE
- DROP TABLE IF EXISTS
- DROP vs. DELETE vs. TRUNCATE
What is the DROP command in SQL?
DROP command in SQL removes table definition and all the data from one or more database tables.
- It is a Data Definition Language (DDL) command.
- It removes all data/indexes/triggers/constraints/ and permission specific to that table
- SQL DROP TABLE statement permanently removes the data from the tables and the metadata that defines the table in the data dictionary.
Note
- Dropping a table or a database is an irreversible process, so be extra careful while dropping the table or database
- SQL DROP TABLE command can’t be used to remove a table that a Foreign Key references
Syntax
The syntax to drop the table is a straightforward DROP TABLE command followed by the table names, i.e.
DROP TABLE table_names;
Note: You can drop multiple tables at a time.
Now, let’s have some examples to understand better how to use the SQL DROP command.
So, firstly we will create a student table of a class that contains the record of a student who scored maximum marks in a particular subject using CREATE TABLE and INSERT command in SQL.
CREATE TABLE Student( RollNo int PRIMARY KEY, Name VARCHAR(100) NOT NULL, Gender TEXT NOT NULL; Subject VARCHAR(30), MARKS INT (3));
INSERT INTO Student VALUES (1, Vaibhav, M, Mathematic, 100);INSERT INTO Student VALUES (2, Vishal, M, Physics, 79);INSERT INTO Student VALUES (3, Saumya, F, Chemistry, 95);INSERT INTO Student VALUES (4, Arun, M, English, 78);INSERT INTO Student VALUES (5, Anjum, F, Hindi, 83);INSERT INTO Student VALUES (6, Radhika, F, Biology, 57);INSERT INTO Student VALUES (7, Harpreet, F, Physical Education, 68);
SELECT * FROM Student;
Output
| RollNo | Name | Gender | Subject | Marks |
| 1 | Vaibhav | M | Mathematics | 100 |
| 2 | Vishal | M | Physics | 79 |
| 3 | Saumya | F | Chemistry | 95 |
| 4 | Arun | M | English | 78 |
| 5 | Anjum | F | Hindi | 83 |
| 6 | Radhika | F | Biology | 57 |
| 7 | Harpreet | F | Education | 68 |
Must Read: SQL SELECT
Now, we will use the above Student table in our examples.
SQL DROP COLUMN
SQL DROP COLUMN is used to delete the specified column(s) from the table or the database.
Syntax
ALTER TABLE table_nameDROP COLUMN column_names;
Must Read: Introduction to SQL
Single Column
Example: Drop the column name Marks from the Student table.
Query
ALTER TABLE StudentDROP COLUMN Marks;
Output
| RollNo | Name | Gender | Subject |
| 1 | Vaibhav | M | Mathematics |
| 2 | Vishal | M | Physics |
| 3 | Saumya | F | Chemistry |
| 4 | Arun | M | English |
| 5 | Anjum | F | Hindi |
| 6 | Radhika | F | Biology |
| 7 | Harpreet | F | Education |
Must Read: UPDATE query in SQL
Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views
Multiple Columns
Example: Drop marks and subject columns from the Student table.
Query
ALTER TABLE StudentDROP COLUMN Marks, Subject;
Output
| RollNo | Name | Gender |
| 1 | Vaibhav | M |
| 2 | Vishal | M |
| 3 | Saumya | F |
| 4 | Arun | M |
| 5 | Anjum | F |
| 6 | Radhika | F |
| 7 | Harpreet | F |
Must Read: SQL Logical Operator
Must Read: Introduction to Normalization
SQL DROP TABLE
SQL DROP TABLE commands are used to delete the specified table from the database.
Syntax
DROP TABLE table_name;
Example: Remove the Student table from the database.
Query
DROP TABLE Student;
We can drop indexes, constraints, and databases like the SQL DROP COLUMN and DROP TABLE.
Syntax for DROP Database
DROP DATABASE database_name;
Syntax for DROP Constraints
ALTER TABLE table_nameDROP CONSTRAINTS constraint name;
Syntax to DROP Index
DROP INDEX index_name;
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
DROP TABLE IF EXISTS
When there are multiple tables in any database, and you want to remove any table from the database but are unsure whether that table exists in the database or not. So, in that case, we can add an optional IF EXISTS command while dropping the table.
Syntax
DROP TABLE IF EXISTS table_name;
Must Read: SQL LIMITS
Must Check: Database and SQL Online Course and Certifications
DELETE vs. TRUNCATE vs. DROP
| DELETE | TRUNCATE | DROP |
| It is a DML command | It is a DDL command. | It is a DDL command |
| DELETE FROM tble_nameWHERE conditions; | TRUNCATE TABLE table_name; | DROP TABLE table_name; |
| It removes one or more records from the table | It removes all the rows from the existing table | It completely removes the table from the database |
| It doesn’t free the allocated space of the table. | It doesn’t free the allocated space of the table. | It completely removes the allocated space for the table from the memory. |
| It is slower than the DROP and TRUNCATE command as it deletes one row at a time base on the specified conditions | It is faster than both the DELETE and TRUNCATE command as it deletes all the records at a time without any condition | It is faster than DELETE but slower than TRUNCATE asit firstly deletes the rows and then the table from the database. |
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
Conclusion
In this article, we have briefly discussed how to use drop command in sql with examples.
Hope this article, will help you in your data science/data analysis journey.
Keep Learning!!
Keep Sharing!!
FAQs
Can I use the DROP command without confirmation?
By default, many database management systems (DBMS) prompt for confirmation before executing the DROP command. However, some DBMSs provide options to disable the confirmation prompt or provide a way to automatically confirm the operation. It is important to be cautious when using these options to avoid accidental deletions.
Are there any restrictions on using the DROP command?
Yes, the DROP command may have restrictions depending on the database system and the user's permissions. Typically, only users with sufficient privileges, such as database administrators or owners of the object, can execute the DROP command. It is important to have the necessary permissions to avoid unauthorized deletions.
Can I recover the dropped objects using the DROP command?
No, the DROP command permanently removes the specified object and its data from the database. Once an object is dropped, it cannot be easily recovered. It is important to exercise caution when using the DROP command and ensure that you have a backup or a plan in place to handle data loss.
Download this article as PDF to read offline
Download as PDF
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Trending Management Courses
Top Picks & New Arrivals
Comments