- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
SQL - DROP Table
SQL provides command to DROP an existing table completely in a database. Once SQL DROP command is issued then there is no way back to recover the table including its data, so be careful before issuing this command in production system.
The SQL DROP Table Statement
The SQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table's definition, and its data, indexes, triggers, constraints and permission specifications (if any).
Note −
You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.
If the table is partitioned, the statement removes the table definition, all its partitions, all data stored in those partitions, and all partition definitions.
To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.
Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely frees the table from the memory.
DROP TABLE causes an implicit commit, except when used with the TEMPORARY keyword.
Syntax
The basic syntax of this DROP TABLE statement is as follows −
DROP TABLE table_name;
Example
Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Let us first verify the CUSTOMERS table using the DESC command then we will delete it from the database −
DESC Table
If the table is created successfully the DESC command displays the structure of the table as shown below −
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int(11) | NO | PRI | NULL | |
NAME | varchar(20) | NO | NULL | ||
AGE | int(11) | NO | NULL | ||
ADDRESS | char(25) | YES | NULL | ||
SALARY | decimal(18,2) | YES | NULL |
This means that the CUSTOMERS table is available in the database, so let us now drop it as shown below.
DROP TABLE CUSTOMERS;
Output
The output is displayed as follows −
Query OK, 0 rows affected (0.001 sec)
Verification
Now, to verify if the table is actually dropped, you can use the DESC CUSTOMERS command as shown −
DESC CUSTOMERS;
Following error is displayed −
ERROR 1146 (42S02): Table 'tutorials.CUSTOMERS' doesn't exist
When a MySQL table is dropped using SQL DROP command, privileges granted specifically for the table are not automatically dropped. They must be dropped manually.
The IF EXISTS Clause
Instead of always checking if the table exists or not in a database before dropping it, you can use the IF EXISTS clause in the DROP TABLE statement.
This clause, when specified in the DROP TABLE query, will automatically check whether the table exists in the current database and then drops it, if yes. If the table does not exist in the database, the query will be ignored.
Syntax
Following is the basic syntax of DROP TABLE IF EXISTS −
DROP TABLE [IF EXISTS] table_name;
Example
If you try to drop a table that doesn't exist in the database, without using the IF EXISTS clause, as shown below −
DROP TABLE CUSTOMERS;
An error will be generated −
ERROR 1051 (42S02): Unknown table 'tutorials.CUSTOMERS'
If you use the IF EXISTS clause along with the DROP TABLE statement as shown below, the specified table will be dropped and if a table with the given name, doesn't exist the query will be ignored.
But if you try to drop a table that does not exist in a database, using the IF EXISTS clause, as shown below −
DROP TABLE IF EXISTS CUSTOMERS;
The query will be ignored with the following output displayed −
Query OK, 0 rows affected, 1 warning (0.001 sec)
DROP - TEMPORARY TABLE
You can include TEMPORARY keyword with DROP TABLE statement which will drop only TEMPORARY tables. Including the TEMPORARY keyword is a good way to prevent accidentally dropping non-TEMPORARY tables.
Syntax
DROP TEMPORARY TABLE TEMP_TABLE;
Example
Following is an example to delete a temporary table CUSTOMERS.
DROP TEMPORARY TABLE CUSTOMERS;
To Continue Learning Please Login
Login with Google