SQL Delete Table Statement With Example

DELETE statement:

The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.

You need to specify table name that you want to remove after the DELETE TABLE keywords. To remove a table, you must have delete table privilege. If the query runs successfully, the table will be removed from the database permanently.

• DELETE permanently removes records from a table.
• DELETE can delete one or more records in a table.
• Use the WHERE clause to DELETE only specific records.

SQL DELETE syntax:

  DELETE table-name  

Syntax: Without using WHERE conditon

There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are not exactly same there are some differences between them.

Difference b/w DROP and TRUNCATE statements

SL. NO	TRUNCATE	DELETE
1	TRUNCATE is a DDL command	DELETE is a DML command
2	TRUNCATE is executed using a table lock and whole table is locked for remove all records.	DELETE is executed using a row lock, each row in the table is locked for deletion.
3	We cannot use Where clause with TRUNCATE.	We can use where clause with DELETE to filter & delete specific records.
4	TRUNCATE removes all rows from a table.	The DELETE command is used to remove rows from a table based on WHERE condition.
5	Minimal logging in transaction log, so it is performance wise faster.	It maintain the log, so it slower than TRUNCATE.
6	Truncate cannot be used with indexed views	Delete can be used with indexed views

Let's assume we are having a table(Student_info) below which has to be deleted.

STD_ID	NAME	CLASS
01	PRAYAG	B.TECH
02	PANKAJ	ARTS
03	RAKESH	M.TECH

Now, Using DELETE statement to delete the table.

DELETE Student_info 

----------------------
1 table deleted;