Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Statements and Clauses

SQL DELETE statement

Reference for SQL DELETE: remove rows with a WHERE condition, delete using subqueries and joins, and know how foreign keys and transactions affect deletes.

DELETE FROM removes rows from a table. A WHERE clause selects the rows to remove; leave it out and every row is deleted, although the table itself, its columns and its indexes stay.

Deletion is permanent once committed. Test the condition with a SELECT first, and do risky deletes inside a transaction.

SQL
DELETE FROM orders
WHERE status = 'cancelled';

Try it

SQL Deleting with a condition ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Deleting rows that have no match ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
DELETE FROM table_name
[WHERE condition];
Part Description
table_name The table to remove rows from.
WHERE Rows for which the condition is true are deleted. Rows where it is false or NULL are kept.

Result: the number of rows deleted. Zero is not an error. Deleting a row that other rows reference fails, or cascades, depending on the foreign key's ON DELETE action.

Dialect differences

Dialect Notes
SQLite DELETE FROM t WHERE ... with subqueries. RETURNING supported (3.35+). LIMIT works only if SQLite was compiled with the option. Plain DELETE FROM t is optimized to empty the table quickly.
PostgreSQL DELETE FROM t USING other WHERE ... to delete by join, plus RETURNING.
MySQL DELETE t FROM t JOIN other ON ... WHERE ...; ORDER BY and LIMIT allowed on single-table deletes.
SQL Server DELETE t FROM t JOIN other ON ... WHERE ...; DELETE TOP (n); OUTPUT deleted.*.
Oracle DELETE FROM t WHERE ... with subqueries; RETURNING ... INTO.
SQL
-- PostgreSQL
DELETE FROM order_items AS oi
USING orders AS o
WHERE o.id = oi.order_id
  AND o.status = 'cancelled';

-- MySQL / SQL Server
DELETE oi
FROM order_items AS oi
JOIN orders AS o ON o.id = oi.order_id
WHERE o.status = 'cancelled';

Usage notes

  • No WHERE means all rows. Run the condition through SELECT first.
  • Foreign keys: deleting a parent that still has children fails, unless the key says ON DELETE CASCADE or SET NULL. In SQLite this is enforced only after PRAGMA foreign_keys = ON. See ON DELETE actions.
  • NOT IN with NULL: NOT IN (subquery) deletes nothing if the subquery returns any NULL. Prefer NOT EXISTS.
  • Deleting everything: TRUNCATE TABLE is faster in databases that have it, but it is DDL and usually cannot be filtered.
  • Soft deletes: many applications set a deleted_at column with UPDATE instead, to keep history and allow undo.
  • Space is not always returned: SQLite and others reuse freed pages; run VACUUM to shrink the file.
  • Triggers fire per deleted row where defined.
  • Huge deletes hold locks and grow logs; delete in batches.