| DELETE (SQL statement) |
|---|
| Name | DELETE |
|---|
| Type | Data Manipulation |
|---|
| Introduced | SQL-86 |
|---|
| Related | UPDATE, INSERT, TRUNCATE |
|---|
DELETE (SQL statement) DELETE is a Structured Query Language statement used to remove rows from a Relational database table. It is part of the SQL standard and is implemented in products from vendors such as Oracle Corporation, Microsoft (Microsoft SQL Server), IBM (Db2), PostgreSQL, MySQL, and SQLite. DELETE interacts with features from ACID transactions, Indexes, triggers, and Foreign key constraints.
Overview
DELETE removes one or more tuples from a relation defined in a schema using a conditional WHERE clause or without one to remove all rows. Implementations differ in how they log changes to the Write-ahead logging or Transaction log and in their handling of referential actions like ON DELETE CASCADE defined by Foreign key. DELETE is distinct from TRUNCATE TABLE and DROP TABLE in semantics, logging, and recoverability; it executes row-level deletion whereas TRUNCATE is a DDL operation commonly associated with minimal logging and reserved for fast bulk removal.
Syntax and Variants
Standard DELETE syntax includes a target table and an optional WHERE predicate, and some dialects support FROM and JOIN clauses for multi-table predicates. Variants and extensions include: - DELETE FROM
WHERE - DELETE with FROM ... JOIN in MySQL and Microsoft SQL Server for correlated deletions. - DELETE RETURNING in PostgreSQL and Oracle to return deleted rows similar to RETURNING clause in INSERT. - Multi-table DELETE supported in MySQL and limited forms in SQLite via CTEs. Implementations interact with Indexes, partitioning, and Query optimizer rules from vendors like Oracle Corporation and Microsoft.
Examples and Use Cases
Common use cases include purging obsolete records, enforcing retention policies, and performing data corrections. Example idioms: - Targeted deletion: DELETE FROM customers WHERE last_order < '2020-01-01' using predicates and indexes from B-tree structures. - Cascading deletes rely on defined Foreign key actions such as ON DELETE CASCADE between tables like orders and order_items. - Bulk archival pattern: INSERT ... SELECT to an archive table in PostgreSQL or MySQL followed by DELETE to preserve audit history, often combined with VACUUM or table maintenance in PostgreSQL and SQLite. - Soft delete: marking rows with a status flag rather than removing them, employed by applications built on Ruby on Rails or Django to support logical deletion. Examples often illustrate interaction with triggers firing BEFORE DELETE or AFTER DELETE in implementations such as Oracle Database and Microsoft SQL Server.
DELETE can be costly for large result sets due to row-by-row logging, index maintenance, and constraint checking. Optimizations include: - Using TRUNCATE for full-table removal in systems where semantics permit, as in Microsoft SQL Server or PostgreSQL. - Deleting in batches to reduce lock contention and log growth, a technique used in high-throughput systems like Amazon Aurora and Google Cloud Spanner. - Dropping or deferring indexes and constraints during mass delete operations in Oracle Database and rebuilding afterward to reduce overhead. - Partition-wise delete by dropping or truncating partitions in partitioned tables supported by PostgreSQL, Oracle, and MySQL for near-constant-time operations. Monitoring tools from Oracle Corporation and Microsoft expose lock waits, IO patterns, and explain plans from the Query optimizer to guide tuning.
Transactional Behavior and Concurrency
DELETE participates in transactional semantics defined by ACID; it is subject to isolation levels such as READ COMMITTED and SERIALIZABLE as implemented by Oracle Corporation, Microsoft SQL Server, PostgreSQL, and MySQL InnoDB. Concurrency effects include row locks, gap locks, and potential deadlocks; vendor specifics introduce features like optimistic concurrency control in PostgreSQL's MVCC and snapshot isolation in Microsoft SQL Server SNAPSHOT isolation. Recovery and rollback depend on the Transaction log and Write-ahead logging implementations; point-in-time recovery in Oracle Database and Db2 relies on these logs to undo DELETE operations.
Security and Permissions
Permission to execute DELETE is controlled by privileges such as DELETE and ownership rules in systems like Oracle Database, Microsoft SQL Server, PostgreSQL, and MySQL. Role-based access control models in Microsoft Active Directory integrated environments and LDAP-backed systems can govern who may issue DELETE statements. Auditing of deletions is commonly configured with tools like Oracle Audit Vault or SQL Server Audit and by using triggers or temporal tables (as in SQL:2011 temporal extensions) to retain historical data for compliance regimes such as GDPR and HIPAA.
Category:SQL
Generated by GPT-5-mini