What is the DELETE Statement?

The DELETE statement is used to remove existing records from a table. It can delete a single row, multiple rows, or all rows, depending on the condition specified in the WHERE clause.

Basic DELETE Syntax

The basic syntax specifies the table to delete from and a condition to identify which rows should be removed.

DELETE FROM table_name
WHERE condition;

Deleting a Single Record

To remove one specific row, use a WHERE condition that uniquely identifies that record.

DELETE FROM employees
WHERE name = 'John Smith';

Deleting Multiple Records

A WHERE condition that matches more than one row will remove every matching record in a single statement.

DELETE FROM employees
WHERE department = 'Temp';

The Danger of Omitting WHERE

If the WHERE clause is omitted, DELETE will remove every row in the table. The table structure remains, but all data inside it is gone.

-- Warning: this deletes ALL rows in the table
DELETE FROM employees;

Deleting with a Subquery

A subquery can be used inside the WHERE clause to delete rows based on conditions found in another table.

DELETE FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE closed = 1
);

DELETE vs TRUNCATE

TRUNCATE TABLE also removes all rows, but it is faster than DELETE because it does not log individual row deletions. However, TRUNCATE cannot be filtered with a WHERE clause and typically cannot be rolled back in some database systems.

TRUNCATE TABLE employees;

Common DELETE Clauses

Advantages of DELETE

• Removes unwanted or outdated records precisely.
• Can target a single row, several rows, or an entire table.
• Supports subqueries for conditional deletion.
• Keeps table structure intact while removing data.
• Works with transactions, allowing changes to be rolled back if supported.

Best Practices

• Always use a WHERE clause unless you intend to delete every row.
• Run a SELECT with the same WHERE condition first to confirm affected rows.
• Back up important data before running large DELETE statements.
• Use transactions for critical deletions when supported.
• Consider TRUNCATE only when you need to clear an entire table quickly.