TRUNCATE, DROP and DELETE in SQL
What if you want to delete the contents of a table from the table or want to delete only some rows satisfying a particular conditions? How can it be done?
For this, we have 3 functions in SQL: DROP, TRUNCATE and DELETE. In this tutorial we shall understand all of these 3 functions in detail.
DROP: Drops a table from the database completely.
TRUNCATE: Deletes all of the rows from the table but does not drop the table from the database.
DELETE: Deletes the content of the table (some rows satisfying a particular condition or all the rows) but does not drop the table from the database.
Syntax: DROP TABLE <tablename>;
After using above query if you write SELECT * FROM <tablename> an error will throw up:
Msg 208, Level 16, State 1, Line 51
Invalid object name 'tablename'
This error pops up because DROP TABLE removes the table completely from the database.
Syntax: TRUNCATE TABLE <tablename>;
After using above query if you write SELECT * FROM <tablename> then a table with 0 rows will be shown as an output. TRUNCATE TABLE empties the table but retains that empty table in the database.
DELETE TABLE <tablename>
WHERE <conditions on rows which need to be deleted>
Eg. DELETE TABLE mytable WHERE department = 'Cardiology';
Above commands delete the rows from my SQL table 'mytable' wherever department column contains the value 'Cardiology'.
If we skip the WHERE command then TRUNCATE and DELETE will have same impact i.e. an empty table gets retained in the database.