DELETE · SQL

DELETE is the SQL data manipulation statement that removes existing rows from a table. In its basic form, DELETE FROM orders WHERE status = 'canceled', it removes every row that satisfies the condition; without a WHERE clause it removes every row in the table while leaving the table structure, indexes and permissions in place.

As part of DML, the SQL DELETE statement is fully transactional in relational databases: deleted rows can be restored with a rollback until the transaction commits, and each removal is checked against foreign key constraints and can fire row-level triggers. These guarantees make it safe and precise, but also slower than bulk alternatives such as TRUNCATE on very large tables.

How the DELETE statement works

The engine processes a DELETE in two logical steps: it first identifies the target rows, much like a SELECT with the same filter, and then removes them one by one while recording each change in the transaction log. Indexes on the table are updated for every removed row, and locks are taken on the affected rows or pages so that concurrent transactions see a consistent state.

Physically, many engines do not erase the data immediately. PostgreSQL marks the old row versions as dead under MVCC, and the space is reclaimed later by VACUUM; InnoDB in MySQL marks records for deletion and a background purge thread removes them. As a result, a large DELETE does not shrink the data file right away, and disk space typically returns only after maintenance or a table rebuild.

The main clauses of the statement determine which rows are removed and what the caller gets back, as summarized below.

Filtering rows with WHERE and subqueries

The precision of a DELETE depends entirely on its filter condition. Simple predicates compare columns with constants, while more complex removals refer to other tables through EXISTS, IN or vendor join syntax. For example, PostgreSQL accepts DELETE FROM sessions s USING users u WHERE s.user_id = u.id AND u.disabled, which removes sessions belonging to disabled accounts.

A reliable habit is to run the same condition as a SELECT first, check the row count and only then change the keyword to DELETE. Inside an explicit transaction, the RETURNING clause or a follow-up count makes it possible to verify the result before committing, which turns a risky operation into a reviewable one.

DELETE compared with TRUNCATE and DROP

Three SQL commands remove data, and they differ sharply in scope, speed and recoverability. DELETE is DML and works row by row, while TRUNCATE and DROP TABLE are DDL operations that act on the table as a whole.

As a rule, use DELETE for selective or audited removals and TRUNCATE when an entire staging or temporary table must be emptied quickly. TRUNCATE usually fails on a table referenced by foreign keys, whereas DELETE respects each reference individually.

Referential integrity and cascading deletes

When other tables reference the target rows through foreign keys, the database applies the referential action defined on each constraint. This behavior decides whether a DELETE succeeds, fails or spreads to related tables, so it must be designed deliberately rather than left to defaults.

ON DELETE CASCADE is convenient but can remove far more data than intended, because cascades can chain across several levels. Many teams therefore restrict cascading to tables that are true parts of their parent and prefer an explicit error everywhere else.

Performance and safety in practice

Removing millions of rows in a single statement creates a long transaction, heavy log growth and extensive locking, and on replicated systems it can cause noticeable replica lag. The usual remedy is batch deletion: removing rows in chunks of a few thousand to tens of thousands, committing after each chunk and pausing briefly if replication falls behind.

• Index the filter columns so the engine does not scan the whole table for each batch.
• Index foreign key columns in child tables, or every parent removal triggers a full scan of the child.
• Prefer dropping or detaching a partition when data is organized by date and whole periods expire.
• Consider soft deletion with a deleted_at column when records must remain recoverable or auditable.
• Remember that privacy regulations may require real removal, so soft deletion alone is not always sufficient.

Finally, granting the DELETE privilege only to the roles that genuinely need it, and keeping tested backups with point-in-time recovery, protects against the most damaging accident in daily database work: a statement executed without its WHERE clause.

The short version

DELETE is the command that removes records from a database table. It is like crossing out entries in a ledger: a rule says which entries go, such as all canceled bookings, and the database removes exactly those.

If no rule is given, every entry is removed. Until the change is confirmed, the database can still undo the whole operation, which is why careful users check what will be removed before confirming.

Where it makes a difference

An online service keeps login session records for 30 days, and the sessions table has grown to 400 million rows. A first attempt to clean it with a single DELETE for all records older than 30 days runs for over an hour, fills the transaction log and delays the read replicas by several minutes, so the administrator cancels it and the database rolls everything back.

The second attempt uses a scheduled job that deletes 10,000 expired rows per batch using an index on the creation timestamp, commits after each batch and waits briefly between them. The cleanup completes overnight with no visible impact on users, and the team later converts the table to daily partitions so that expired days can be dropped in seconds.