SQL DELETE

What does the SQL DELETE statement do?

DELETE removes one or more rows from a table based on an optional search condition.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DELETE: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift and most ANSI-compliant databases

SQL DELETE Full Explanation

DELETE is a Data Manipulation Language (DML) statement that permanently removes rows from a table. When a WHERE clause is supplied, only matching rows are deleted; without it, all rows in the target table are removed. The operation obeys transaction rules: changes are visible only after COMMIT and can be undone with ROLLBACK. Foreign-key constraints, ON DELETE triggers, and cascading rules may prevent or propagate deletions. DELETE acquires row or table locks depending on the database, which can affect concurrency. Some dialects (PostgreSQL, SQL Server, SQLite) support a RETURNING / OUTPUT clause to return the deleted rows or specific columns. Large deletes can be batched with ORDER BY and LIMIT/TOP to mitigate locking and log growth. Unlike TRUNCATE, DELETE logs every removed row, fires triggers, and can be filtered.

SQL DELETE Syntax

DELETE FROM table_name
[WHERE search_condition]
[ORDER BY sort_expression]
[LIMIT row_count | TOP (row_count)]
[RETURNING column_list];

SQL DELETE Parameters

table_name (identifier) - Target table holding rows to remove
search_condition (expression) - Boolean predicate that filters rows
sort_expression (expression) - Column(s) used to order deletions (supported in MySQL, PostgreSQL)
row_count (integer) - Maximum number of rows to delete in this statement
column_list (list) - Columns to return from deleted rows (RETURNING/OUTPUT)

Example Queries Using SQL DELETE

-- Delete a single user by primary key
DELETE FROM users
WHERE id = 42;

-- Delete all inactive accounts older than 1 year and return email addresses (PostgreSQL)
DELETE FROM accounts
WHERE active = FALSE
  AND last_login < CURRENT_DATE - INTERVAL '1 year'
RETURNING email;

-- Batch delete 100 oldest log records (MySQL)
DELETE FROM app_logs
ORDER BY created_at
LIMIT 100;

Expected Output Using SQL DELETE

Matched rows are removed from the table
The DBMS returns the number of affected rows and, if supported, the data specified in RETURNING/OUTPUT
Related triggers execute and foreign-key cascades occur

Use Cases with SQL DELETE

Removing obsolete or incorrect data
Implementing soft-delete by copying rows to an archive table via trigger, then deleting
Cleaning staging tables after ETL loads
Purging logs or temporary data in scheduled maintenance jobs
Cascading deletions in parent-child relationships

Common Mistakes with SQL DELETE

Omitting the WHERE clause and deleting every row unintentionally
Forgetting to COMMIT in transactional databases, causing the delete to be rolled back
Ignoring foreign-key constraints that block deletion of referenced rows
Misusing LIMIT without ORDER BY, leading to non-deterministic deletions
Expecting DELETE to reset identity/auto-increment counters (use TRUNCATE or ALTER SEQUENCE instead)

First Introduced In

SQL-92

Frequently Asked Questions

What happens if I run DELETE without a WHERE clause?

Every row in the target table is removed. Always double-check your filter conditions or run the statement inside a transaction you can roll back.

How can I improve performance on large deletes?

Batch the delete with LIMIT/TOP and ORDER BY, disable indexes where safe, increase log file size, or switch to partition exchange/truncate strategies.

Will DELETE reset an auto-increment (IDENTITY) column?

No. The sequence continues from its last value. Use TRUNCATE or explicitly reseed the sequence if you need to restart numbering.

Can I capture deleted rows for auditing?

Yes. Use a RETURNING/OUTPUT clause, AFTER DELETE triggers, or CDC features to record the affected rows in an audit table or log.