Wednesday, April 19, 2023

How to use DROP command to remove tables in Oracle, MySQL and SQL Server

Hello guys, if you want to learn about DROP command in SQL then you have come to the right place. Earlier, I have shared the best free SQL and Database courses and several tutorials to learn SELECT, GROUP BY, and other important commands, and in this article, I will show you to use the DROP command in SQL. DROP is one of the basic SQL commands, which is used to DROP database objects. Since it's part of ANSI SQL, the DROP command is supported by all major database vendors, including Oracle, MySQL, and Microsoft SQL Server. A SQL DROP TABLE statement is used to delete a table definition and all data from a table, but DROP can also be used to drop index, view, trigger, or any other database object.

By the way, be careful with using the DROP table command because once a table is deleted, all the information available in the table is lost forever.

DROP is sometimes also used to clear tables; for example, removing a large table with millions of records by using the SQL DELETE command may take a long time; alternatively, you can DROP and recreate the table. In fact, the difference between TRUNCATE, DELETE, and DROP is one of the popular SQL Interview questions.

Though DELETE allows you to remove selective records, DROP doesn't provide that option. Also, DELETE is a DML command and can be rolled back, but DROP cannot be rolled back. DROP will also remove all indexes, rows, and privileges, and no DML triggers will be fired.

Though DROP behavior is more or less consistent across MySQL, Oracle, and SQL Server, there are some differences like from Oracle 10g table can be "undropped". Execution of the DROP command is also controlled by access control, so you just cannot drop a table if you don't have sufficient privileges.

Btw, If you are new to SQL and don't understand fundamentals like ALTER, DROP, or co-related sub-queries then I highly recommend you go through a comprehensive SQL course like The Complete SQL Bootcamp by Jose Portilla on Udemy. It's one of the best and also most affordable courses to learn SQL online

How to DROP tables in Oracle, MySQL, and SQL Server

Let's see the syntax to drop the table from the database.

DROP TABLE "table_name";

This syntax is the purest form, which DROP table with "table_name" in the current database or schema.

1. SQL DROP TABLE Example in MySQL

Let's see the command to drop a table from the MySQL database.

DROP TABLE Session;

2. SQL DROP TABLE Example in Oracle

Let's see the command to drop a table from the Oracle database:

DROP TABLE marketing_emp;

3. How to drop table in Microsoft SQLServer

Here is the SQL DROP table command for Microsoft SQL Server. It's actually similar to MySQL and Oracle. The following command will drop the table Employee in your current database or selected schema.

If you are running this from an SQL Script, you can either use the USE database command or can prefix, schema name to the table name. This will give enough hints to the database about dropping a table from which schema.

// drop table from the currently selected schema
DROP TABLE Employee;   

// drop table from company schema
USE company;
DROP TABLE Employee;   

// another way to drop table from the arbitrary schema
DROP TABLE company.Employee;

If you want to learn more about the DROP command in SQL Server then you can also check out Microsoft SQL for Beginners course on Udemy. It's a great course and also very affordable, you can buy in just $10 in several Udemy sales

DROP table Command Example in Oracle, MySQL and SQL Server

Things to remember about DROP command in SQL

1) DROP is a DDL command.

2) DROP Command cannot be rolled back, except Oracle 10g which provides additional functionality to "undrop" a table as shown in the following example :

SQL> FLASHBACK TABLE Product TO BEFORE DROP;

Flashback complete.

3) DROP table command removes all rows, indexes, and privileges for that table.

4) No DML triggers will be fired as a result of the DROP table command.

5) DROP can be used to clear the database by dropping and recreating them.

6) Difference between the DROP and the TRUNCATE command is, DROP command will delete the entire row also the structure. But the TRUNCATE command will only remove the contents only, not the structure, so no need to give specifications for another table creation.

That's all about how to drop a table in MySQL, SQL Server, and Oracle. We have not only see DROP table command examples in these three primary databases but also learned a bit about the DROP command in SQL.

Since two databases/schema may contain tables with the same name, it's wise to prefix schema name before table name while using the DROP command in SQL Server or Oracle. If you do use the DROP command for database clean-up, remember it cannot be rolled back, and no DML trigger is fired.

Other Useful Programming Resources You May Like:

Thanks for reading this article so far. If you like this SQL DROP command tutorial then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.

P. S. - If you are new to SQL and Database in general and want to learn DB fundamentals and SQL basics before deep dive on PostgreSQL then you can also check out this list of free Websites to learn SQL and Database for beginners.

Java67

Pages