DROP TABLE Statement

The DROP TABLE statement is used to drop one or more existing tables from the database. It removes the table definition and all the data inside the table, so be careful while using this statement.

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] …

[RESTRICT | CASCADE]

You can also specify the IF EXISTS clause to drop the table only if it exists. If the IF EXISTS clause is not provided while dropping a non-existent table, it will throw an error.

The TEMPORARY keyword has the following effects:

• When the TEMPORARY keyword is specified, the statement drops only temporary tables.
• When the TEMPORARY keyword is specified, the statement doesn’t cause any implicit commits.

Let’s start by creating some tables and inserting records in it. We will later use these tables in the subsequent examples to understand the DROP TABLE statement better.

The first table we are going to create is the EMPLOYEE table. The following query creates the table and then later inserts records in it

-- create employee table
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

-- insert into employee table
INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (3, 'Ava', 'Sales');

We will run the following query to fetch the initial data in the table:

SELECT * FROM EMPLOYEE;

The following is the initial data in the table:

The second table, we are going to create is the MANAGER table. The following query creates the table and then later inserts records in it:

-- create manager table
CREATE TABLE MANAGER (
  managerId INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

-- insert into manager table
INSERT INTO MANAGER VALUES (99, 'Jake');
INSERT INTO MANAGER VALUES (98, 'Smith');
INSERT INTO MANAGER VALUES (97, 'Lucy');

We will run the following query to fetch the initial data in the table:

SELECT * FROM MANAGER;

The following is the initial data in the table

The tables that we have created till are all permanent tables, i.e. they will be persisted even after the session is closed. Now lets create a temporary table. The temporary tables are not persisted and are only present till the duration of the current session. We will create a temporary table ORGANISATION using the records we already have in the EMPLOYEE and MANAGER tables. The following query creates the table and populates it with records:

-- create temporary table organisation
CREATE TEMPORARY TABLE ORGANISATION 
AS
SELECT empId, name FROM EMPLOYEE
UNION ALL
SELECT managerId, name FROM MANAGER;

We will run the following query to fetch the initial data in the table:

SELECT * FROM ORGANISATION;

The following is the initial data in the table

MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides a variety of rich features to create databases and tables, insert data in them, and further manipulate them as the system evolves. In this article, we are going to have a look at one such functionality, the DROP TABLE statement.