SQL DELETE — Complete Guide to Removing Database Records
DELETE removes rows from a table based on a WHERE condition. Without WHERE, it removes every row. That distinction makes DELETE simultaneously the simplest and the most dangerous DML statement — one missing clause turns a targeted cleanup into a table wipe.
This guide covers safe deletion patterns, the difference between DELETE and TRUNCATE, batch deletes for large tables, foreign key CASCADE behavior, and auditing strategies across PostgreSQL, MySQL, and SQL Server.
SQL DELETE removes rows that match a WHERE condition. Always test with a matching SELECT first, and wrap critical deletes in a transaction so you can ROLLBACK. For bulk removal of all rows, TRUNCATE is faster and resets identity columns. For large-scale deletes, process in batches to avoid lock escalation and transaction log bloat. PostgreSQL's RETURNING clause and SQL Server's OUTPUT clause let you capture deleted rows.
DELETE Syntax and WHERE Clause
The basic syntax is minimal:
DELETE FROM employees WHERE employee_id = 101;
The WHERE clause is not technically required, but omitting it deletes every row in the table. Side by side, the difference is one line:
-- Deletes one row
DELETE FROM orders WHERE order_id = 5042;
-- Deletes ALL rows — probably not what you meant
DELETE FROM orders;
Common WHERE patterns:
-- Multiple conditions
DELETE FROM products
WHERE category = 'Electronics' AND price < 10 AND stock_quantity = 0;
-- IN operator for specific IDs
DELETE FROM customers WHERE customer_id IN (15, 23, 47, 89);
-- BETWEEN for date ranges
DELETE FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31';
-- IS NULL for cleanup
DELETE FROM contacts WHERE email IS NULL AND phone IS NULL;
DELETE with Subqueries and JOINs
The most common pattern is DELETE with a subquery — remove rows that match (or do not match) data in another table:
-- Remove employees in a specific department
DELETE FROM employees
WHERE department_id IN (
SELECT department_id FROM departments WHERE department_name = 'Temporary'
);
-- Remove orphaned records (customers with no orders)
DELETE FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
Standard SQL does not include DELETE ... JOIN syntax. PostgreSQL provides DELETE ... USING as the standards-compliant alternative. MySQL and SQL Server support DELETE ... JOIN as vendor extensions, but the syntax is not portable between databases.
— PostgreSQL, DELETE documentation
MySQL and SQL Server support DELETE with JOIN directly:
-- MySQL / SQL Server
DELETE e
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Temporary';
-- PostgreSQL equivalent using USING
DELETE FROM employees e
USING departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Temporary';
DELETE vs TRUNCATE vs DROP
Three commands remove data, but they work differently and serve different purposes.
DELETE is for removing specific rows. It fires triggers, can be rolled back in a transaction, and leaves the table structure intact. On large tables it is slow because it logs every row individually.
TRUNCATE removes all rows from a table. It is much faster than DELETE FROM table (without WHERE) because it deallocates data pages instead of logging each row. TRUNCATE resets identity/auto-increment counters. In PostgreSQL and SQL Server, TRUNCATE is transactional; in MySQL with InnoDB, it performs an implicit commit.
DROP removes the table itself — structure, data, indexes, constraints, permissions. It is irreversible outside of a backup restore.
Safe Deletion Patterns
The SELECT-first pattern is the most important safety practice. Write the WHERE clause, run it with SELECT to verify which rows match, then replace SELECT with DELETE:
-- Step 1: verify
SELECT order_id, customer_id, status
FROM orders
WHERE status = 'cancelled' AND order_date < '2024-01-01';
-- 847 rows — matches expectations
-- Step 2: delete inside a transaction
BEGIN;
DELETE FROM orders
WHERE status = 'cancelled' AND order_date < '2024-01-01';
-- 847 rows affected — matches the SELECT count
COMMIT;
Soft delete avoids physical deletion entirely. Instead of DELETE, run an UPDATE that marks the row as deleted:
UPDATE customers
SET deleted_at = NOW()
WHERE customer_id = 42;
Application queries then filter with WHERE deleted_at IS NULL. The data stays recoverable. The trade-off: table size grows, indexes include dead rows, and every query must remember the filter.
Archive-before-delete copies rows to an archive table before removing them. This combines INSERT ... SELECT with DELETE:
BEGIN;
INSERT INTO archived_orders
SELECT * FROM orders
WHERE order_date < '2023-01-01' AND status = 'completed';
DELETE FROM orders
WHERE order_date < '2023-01-01' AND status = 'completed';
COMMIT;
Batch Deletes for Large Tables
Deleting millions of rows in a single statement is dangerous for the same reasons as large updates: lock escalation, transaction log growth, and replication lag.
In SQL Server, the transaction log for a single DELETE of 10 million rows can grow to 50+ GB before the transaction commits. If the log file runs out of space, the entire transaction rolls back — which takes even longer than the original delete.
— Microsoft, Transaction Log Architecture documentation
Batch deletion patterns by database:
-- SQL Server: TOP(N) in a loop
WHILE 1 = 1
BEGIN
DELETE TOP(5000) FROM orders
WHERE order_date < '2023-01-01' AND status = 'completed';
IF @@ROWCOUNT = 0 BREAK;
WAITFOR DELAY '00:00:01'; -- optional breathing room
END
-- MySQL: LIMIT-based
DELETE FROM orders
WHERE order_date < '2023-01-01' AND status = 'completed'
LIMIT 5000;
-- Repeat in application code until 0 rows affected
-- PostgreSQL: CTE-based
WITH batch AS (
SELECT order_id FROM orders
WHERE order_date < '2023-01-01' AND status = 'completed'
LIMIT 5000
)
DELETE FROM orders WHERE order_id IN (SELECT order_id FROM batch);
Foreign Key Constraints and CASCADE
Attempting to delete a row that is referenced by a foreign key in another table raises an error. The behavior depends on how the foreign key was defined.
RESTRICT (the default in most databases) blocks the delete and raises an error. This is the safest option — it forces you to delete dependent records first.
CASCADE automatically deletes all child rows when the parent is deleted. Convenient, but dangerous — deleting one parent row can cascade through multiple levels of foreign keys and remove thousands of rows. Always check which tables have CASCADE relationships before deleting.
SET NULL sets the foreign key column in child rows to NULL instead of deleting them. Useful when the child records have independent value (e.g., keeping order_items for accounting after removing the parent order).
To check which tables reference a given table before deleting:
-- PostgreSQL: find foreign keys referencing a table
SELECT tc.table_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND kcu.table_name = 'orders';
Database-Specific Features
PostgreSQL supports DELETE ... USING for join-based deletes and RETURNING to capture deleted rows:
DELETE FROM employees
WHERE salary < 30000
RETURNING employee_id, first_name, last_name;
MySQL supports multi-table DELETE to remove rows from multiple tables in one statement:
DELETE customers, orders
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
WHERE customers.status = 'inactive';
MySQL also supports DELETE ... IGNORE which suppresses foreign key errors and skips rows that would cause violations.
SQL Server provides the OUTPUT clause (equivalent to PostgreSQL's RETURNING) and TOP(N) DELETE for batch operations:
DELETE FROM products
OUTPUT DELETED.product_id, DELETED.product_name
WHERE discontinue_date < GETDATE();
Auditing Deleted Data
In regulated industries — finance, healthcare, government — you cannot just delete records. Compliance requires a trail of what was deleted, when, and by whom.
The most common approach is an audit trigger that copies deleted rows to an audit table:
-- Works on PostgreSQL; MySQL and SQL Server have similar trigger syntax
CREATE OR REPLACE FUNCTION audit_employee_delete()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO employee_audit (employee_id, action, deleted_by, deleted_at, row_data)
VALUES (OLD.employee_id, 'DELETE', current_user, NOW(), row_to_json(OLD));
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_employee_delete();
An alternative is Change Data Capture (CDC), which captures all changes (inserts, updates, deletes) at the database level without triggers. SQL Server has built-in CDC; PostgreSQL uses logical replication; MySQL uses the binlog.
Knowing which tables feed downstream reports before deleting data prevents broken dashboards. Data lineage in a data catalog shows these dependencies. Dawiso's lineage view traces which reports and models consume a given table, so teams can assess the impact of a DELETE operation before running it. This matters for data governance — deletion decisions should consider downstream effects, not just the immediate table.
