DELETE in SQL

🗑ī¸ The DELETE statement in SQL is used to remove one or more rows from a database table. It is one of the core Data Manipulation Language (DML) statements and allows you to delete specific records using conditions or remove every record from a table while keeping the table structure intact.

📖 What is the DELETE Statement?

The DELETE statement removes existing rows from a table. You typically use a WHERE clause to specify which rows should be deleted. If the WHERE clause is omitted, every row in the table is deleted.

Information

The DELETE statement removes data only. The table definition, columns, indexes, and constraints remain unchanged.

đŸŽ¯ Why Use DELETE?

The DELETE statement is commonly used to remove outdated, incorrect, or unnecessary records from a database.

  • 📌 Remove obsolete records.
  • 📌 Delete incorrect or duplicate data.
  • 📌 Clean up test or temporary data.
  • 📌 Archive data before permanent removal.
  • 📌 Maintain accurate and organized databases.

📊 Sample Table

Consider the following Students table:

StudentIDNameDepartmentAgeMarks
101AliceComputer Science2092
102BobMathematics2185
103CharliePhysics2278
104DavidComputer Science2095

📝 Basic Syntax

DELETE Syntax

DELETE FROM table_name
WHERE condition;

💡 Delete a Single Row

Delete the student whose StudentID is 103.

Delete One Record

DELETE FROM Students
WHERE StudentID = 103;

Result:

StudentIDNameDepartment
101AliceComputer Science
102BobMathematics
104DavidComputer Science

📋 Delete Multiple Rows

Use a condition that matches multiple rows to delete all of them.

Delete Multiple Records

DELETE FROM Students
WHERE Department = 'Computer Science';

🌍 Delete All Rows

Omitting the WHERE clause deletes every row from the table.

Delete All Records

DELETE FROM Students;

Warning

Executing a DELETE statement without a WHERE clause removes every row from the table. Ensure this is your intended action before running the query.

🔍 DELETE with Subquery

A subquery can be used to determine which rows should be deleted.

Delete Using a Subquery

DELETE FROM Students
WHERE Department IN
(
    SELECT Department
    FROM Departments
    WHERE Status = 'Inactive'
);

📊 DELETE with EXISTS

The EXISTS operator can also be used to delete rows based on the existence of related records.

Delete Using EXISTS

DELETE FROM Students s
WHERE EXISTS
(
    SELECT 1
    FROM GraduatedStudents g
    WHERE g.StudentID = s.StudentID
);

⚖ī¸ DELETE vs TRUNCATE vs DROP

FeatureDELETETRUNCATEDROP
Removes Rows✅ Yes✅ Yes✅ Yes
Supports WHERE✅ Yes❌ No❌ No
Removes Table Structure❌ No❌ No✅ Yes
Typical UseDelete selected rows.Remove all rows quickly.Remove the entire table.

đŸ’ŧ Real-World Example

A university wants to remove records of students who have graduated and whose data has already been archived.

Delete Archived Students

DELETE FROM Students
WHERE StudentID IN
(
    SELECT StudentID
    FROM GraduatedStudents
);

This query deletes only students whose records already exist in the GraduatedStudents table.

⚠ī¸ Common Mistakes

  • ❌ Forgetting the WHERE clause and deleting every row.
  • ❌ Using an incorrect filter condition.
  • ❌ Deleting records without first creating a backup.
  • ❌ Ignoring foreign key constraints that may prevent deletion.

Warning

Before executing a DELETE statement, run the same WHERE condition in a SELECT query to confirm which rows will be removed.

⚠ī¸ Best Practices

Best Practice

Always include a WHERE clause unless you intentionally want to remove every row. Verify the affected rows with a SELECT query, use transactions for critical deletions, create backups when necessary, and understand foreign key relationships before deleting related data.

🚀 Key Points to Remember

  • 📌 DELETE removes existing rows from a table.
  • 📌 The WHERE clause controls which rows are deleted.
  • 📌 Omitting WHERE deletes every row in the table.
  • 📌 The table structure remains unchanged after deletion.
  • 📌 Subqueries and EXISTS can be used for advanced deletion conditions.
  • 📌 DELETE, TRUNCATE, and DROP serve different purposes.
>>"The DELETE statement removes unwanted data while preserving the structure of your database."

Summary

✅ The DELETE statement is a fundamental SQL command used to remove records from a table. By combining it with conditions, subqueries, and transactions, you can safely delete only the data you intend to remove while maintaining the integrity of your database.