DELETE Statement in SQL

The DELETE statement in SQL is used to remove one or more rows from a table based on a condition. It is commonly used when data needs to be removed permanently from a database.

Syntax for DELETE

DELETE FROM table_name
WHERE condition;

table_name: The table from which rows will be deleted.
WHERE condition: Specifies which rows to delete. If omitted, ALL rows will be deleted (use with caution).

Basic Examples

Delete a Specific Row

DELETE FROM Students
WHERE StudentID = 101;

This removes the student with StudentID = 101 from the Students table.

Delete Multiple Rows

sqlCopyEditDELETE FROM Students
WHERE Age < 13;

This deletes all students younger than 13 years.

Delete All Rows (Use with Caution!)

DELETE FROM Students;

This removes all rows from the Students table, but the table structure remains.

Variants of DELETE

1. Using Subquery

DELETE FROM Orders
WHERE CustomerID IN (
    SELECT CustomerID FROM Customers WHERE Region = 'North'
);

Deletes orders placed by customers from the 'North' region.

2. Using DELETE with JOIN (MySQL, PostgreSQL)

DELETE Orders
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Region = 'North';

Removes orders where the customer belongs to the 'North' region.

3. Using RETURNING Clause (PostgreSQL, Oracle)

DELETE FROM Products
WHERE Category = 'Expired'
RETURNING ProductID, Name;

Deletes products in the 'Expired' category and returns the deleted ProductID and Name.

Explaining the Statement

How DELETE Works

Identifies the rows that match the WHERE condition.
Removes the selected rows permanently from the table.
If no WHERE clause is provided, all rows in the table will be deleted.

What Happens Behind the Scenes?

The database locks the affected rows to prevent conflicts.
Foreign key constraints can prevent deletion if referenced in another table.
Deleted data cannot be recovered unless a backup or transaction rollback is used.

Common Errors and Solutions

Error	Cause	Solution
No rows affected	The `WHERE` condition didn't match any rows.	Verify the condition with a `SELECT` statement.
Cannot delete due to foreign key	Another table references the row.	Delete related rows first or disable constraints.
Deletes all rows unintentionally	Missing `WHERE` clause.	Always include a `WHERE` clause when needed.

SQL Representation

Create Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Class VARCHAR(10)
);

Insert Data

INSERT INTO Students (StudentID, Name, Age, Class) 
VALUES 
(101, 'Alice', 14, '8th'),
(102, 'Bob', 13, '8th'),
(103, 'Charlie', 15, '8th');

Delete Data

DELETE FROM Students
WHERE StudentID = 101;

This removes Alice from the Students table.

Key Points

Always use a WHERE clause unless you intentionally want to delete all rows.
Use DELETE when you want to remove specific rows while keeping the table structure.
If you want to delete all rows faster, consider TRUNCATE TABLE instead (which resets auto-increment counters).
For safety, test your DELETE statement with SELECT first to see which rows will be affected.