Modification
The DELETE Statement
The DELETE statement removes rows from a table.
Overview
The DELETE statement removes rows from a table. It is a Data Manipulation Language (DML) command, part of the SQL standard since SQL-86. Unlike DROP TABLE, which removes the table definition, DELETE only removes data and leaves the table structure, constraints, and indexes intact. DELETE can be transactional, meaning changes can be rolled back if the statement is executed within a transaction. The statement can delete all rows or a subset based on a WHERE clause. Without a WHERE clause, DELETE removes all rows from the table, but the table remains. DELETE is often compared to TRUNCATE TABLE, which is a DDL operation that removes all rows more efficiently but cannot be used with a WHERE clause and typically cannot be rolled back in some databases. DELETE fires triggers and checks constraints, such as foreign keys, which can prevent deletion if referenced rows exist. The statement is widely supported across relational database systems, though syntax for joins or limiting rows may vary. Understanding DELETE is essential for managing data lifecycle and maintaining referential integrity.
Syntax and usage
The basic syntax of DELETE is: DELETE FROM table_name [WHERE condition];. The WHERE clause is optional; if omitted, all rows are deleted. Standard SQL also allows DELETE FROM table_name WHERE condition with no additional clauses. Some databases support DELETE FROM table_name WHERE condition ORDER BY column LIMIT n (MySQL) or DELETE FROM table_name WHERE ctid IN (SELECT ctid FROM table_name WHERE condition LIMIT n) (PostgreSQL). The DELETE statement can also be used with a join in some systems, e.g., DELETE t1 FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.flag = 1 (MySQL). Standard SQL defines a searched DELETE and a positioned DELETE (for cursors). The statement returns the number of rows deleted, often accessible via GET DIAGNOSTICS or vendor-specific functions like ROW_COUNT(). DELETE can be used with a subquery in the WHERE clause, e.g., DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE inactive = 1). Always ensure the WHERE clause correctly identifies the intended rows to avoid accidental data loss.
How it works
When a DELETE statement is executed, the database engine parses the statement, validates it against the schema, and creates an execution plan. The plan identifies the rows to be deleted based on the WHERE clause. The engine then acquires necessary locks on the affected rows and possibly the table. For each row, the engine checks constraints, such as foreign key constraints, to ensure that deletion does not violate referential integrity. If a foreign key references the row and the constraint is not deferrable or set to CASCADE, the deletion may be rejected. Triggers defined for DELETE are fired before or after the deletion, depending on the trigger timing. The actual removal of rows involves marking them as deleted and, in most databases, writing the changes to the transaction log to support rollback and recovery. The space occupied by deleted rows is not immediately reclaimed; it may be reused by subsequent inserts. In databases with MVCC (Multi-Version Concurrency Control), deleted rows remain visible to older transactions until they commit. After all checks and triggers, the statement completes, and the transaction can be committed or rolled back.
Examples
Delete all rows from a table: DELETE FROM employees;. Delete rows matching a condition: DELETE FROM employees WHERE department = 'Sales';. Delete using a subquery: DELETE FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE status = 'Inactive');. Delete with a join (MySQL): DELETE o FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.status = 'Inactive';. Delete with a limit (MySQL): DELETE FROM logs WHERE created_at < '2020-01-01' ORDER BY created_at LIMIT 1000;. Delete with a Common Table Expression (PostgreSQL): WITH old_orders AS (SELECT order_id FROM orders WHERE order_date < '2020-01-01') DELETE FROM orders WHERE order_id IN (SELECT order_id FROM old_orders);. In all cases, verify the WHERE clause with a SELECT statement first to ensure only intended rows are affected.
Common pitfalls
Omitting the WHERE clause deletes all rows, which is a frequent cause of accidental data loss. Always test the WHERE condition with a SELECT before executing DELETE. Another pitfall is neglecting foreign key constraints: attempting to delete a parent row that is referenced by a child row will fail unless the foreign key is defined with ON DELETE CASCADE or the child rows are deleted first. Triggers can also cause unexpected side effects, such as cascading deletes or logging. In databases with replication, DELETE statements are replicated, but large deletes can cause replication lag. Locking behavior can lead to blocking and deadlocks, especially when deleting many rows. Some databases do not support ORDER BY or LIMIT in DELETE, requiring alternative approaches. The DELETE statement does not reset auto-increment counters; use TRUNCATE for that. Finally, DELETE can be slow for large tables due to logging and trigger overhead; consider batching deletes to reduce transaction size and lock contention.
Best practices
Always use a WHERE clause unless you intend to delete all rows. Before executing a DELETE, run a SELECT with the same conditions to verify the affected rows. Wrap DELETE in a transaction so you can roll back if mistakes occur. For large deletions, batch the operation into smaller chunks to avoid long locks and excessive transaction log growth. Consider using TRUNCATE TABLE when you need to remove all rows and do not require triggers or WHERE conditions. Ensure foreign key constraints are properly defined with appropriate ON DELETE actions (CASCADE, SET NULL, RESTRICT) to maintain referential integrity. Test DELETE statements on a development or staging environment first. Use indexes on columns referenced in the WHERE clause to improve performance. Avoid deleting rows that are referenced by other tables unless you handle dependencies. Document and review deletion logic, especially in production. Regularly back up data to mitigate accidental loss. Monitor performance and adjust batch sizes as needed.
Frequently asked questions
Q: What is the difference between DELETE and TRUNCATE? A: DELETE removes rows one by one, can be filtered with WHERE, fires triggers, and can be rolled back. TRUNCATE removes all rows quickly, does not fire triggers, and cannot be filtered; it is a DDL operation and may not be rollback-able in some databases. Q: Can I undo a DELETE? A: If the DELETE was executed within a transaction that has not been committed, you can roll it back. After commit, you need to restore from a backup or use point-in-time recovery if supported. Q: How do I delete rows from multiple tables? A: Standard SQL does not support multi-table DELETE directly. Some databases allow DELETE with joins (e.g., MySQL) or using subqueries. Alternatively, delete from child tables first, then parent tables, respecting foreign key constraints.