SQL - DELETE Statement
The DELETE statement removes one or more rows from a table. The fundamental syntax requires only the table name, but production code should always include a WHERE clause to avoid catastrophic data...
Key Insights
- DELETE removes existing rows from a table based on WHERE conditions, while omitting WHERE deletes all rows—use with extreme caution and always within transactions for safety
- Modern SQL provides DELETE with JOIN syntax, CTEs, and RETURNING clauses for complex deletion scenarios, though syntax varies significantly across database systems
- Soft deletes using UPDATE with status flags often prove more practical than hard deletes in production systems, preserving data lineage and enabling audit trails
Basic DELETE Syntax
The DELETE statement removes one or more rows from a table. The fundamental syntax requires only the table name, but production code should always include a WHERE clause to avoid catastrophic data loss.
-- Delete specific rows
DELETE FROM employees
WHERE department_id = 5;
-- Delete with multiple conditions
DELETE FROM orders
WHERE order_date < '2023-01-01'
AND status = 'cancelled';
-- Dangerous: deletes ALL rows
DELETE FROM temp_data;
Always verify your WHERE clause by running a SELECT first:
-- Test your condition
SELECT * FROM employees WHERE department_id = 5;
-- Then execute delete
DELETE FROM employees WHERE department_id = 5;
DELETE with Subqueries
Subqueries enable complex deletion logic based on related table data. This pattern appears frequently when cleaning up orphaned records or removing data based on aggregated conditions.
-- Delete orders for inactive customers
DELETE FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE status = 'inactive'
);
-- Delete duplicate records, keeping the oldest
DELETE FROM products
WHERE product_id NOT IN (
SELECT MIN(product_id)
FROM products
GROUP BY product_name, sku
);
-- Delete using correlated subquery
DELETE FROM order_items oi
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.order_id = oi.order_id
);
DELETE with JOINs
PostgreSQL, MySQL, and SQL Server support DELETE with JOIN syntax, though implementations differ. This approach often performs better than subqueries for large datasets.
PostgreSQL:
DELETE FROM order_items
USING orders
WHERE order_items.order_id = orders.order_id
AND orders.status = 'cancelled';
-- Multiple table join
DELETE FROM inventory
USING products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE inventory.product_id = p.product_id
AND c.discontinued = true;
MySQL:
DELETE oi FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'cancelled';
-- Delete from multiple tables simultaneously
DELETE oi, od FROM order_items oi
INNER JOIN order_details od ON oi.order_id = od.order_id
WHERE oi.order_date < '2022-01-01';
SQL Server:
DELETE oi
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'cancelled';
DELETE with CTEs
Common Table Expressions provide readable, maintainable deletion logic for complex scenarios. CTEs execute before the DELETE, making them ideal for multi-step filtering.
-- PostgreSQL/SQL Server
WITH inactive_accounts AS (
SELECT account_id
FROM accounts
WHERE last_login < CURRENT_DATE - INTERVAL '365 days'
AND account_type = 'free'
),
orphaned_sessions AS (
SELECT session_id
FROM sessions s
WHERE s.account_id IN (SELECT account_id FROM inactive_accounts)
)
DELETE FROM sessions
WHERE session_id IN (SELECT session_id FROM orphaned_sessions);
-- Recursive CTE for hierarchical deletion
WITH RECURSIVE category_tree AS (
SELECT category_id FROM categories WHERE category_id = 100
UNION ALL
SELECT c.category_id
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.category_id
)
DELETE FROM products
WHERE category_id IN (SELECT category_id FROM category_tree);
RETURNING Clause
PostgreSQL and Oracle support RETURNING to capture deleted data, useful for auditing or cascading operations without separate SELECT queries.
-- PostgreSQL
DELETE FROM employees
WHERE termination_date < '2023-01-01'
RETURNING employee_id, name, department_id;
-- Store deleted records
CREATE TEMP TABLE deleted_orders AS
DELETE FROM orders
WHERE status = 'cancelled' AND order_date < '2023-01-01'
RETURNING *;
-- Use returned values in application logic
WITH deleted AS (
DELETE FROM inventory
WHERE quantity = 0
RETURNING product_id, warehouse_id
)
INSERT INTO audit_log (action, product_id, warehouse_id, deleted_at)
SELECT 'DELETE', product_id, warehouse_id, CURRENT_TIMESTAMP
FROM deleted;
Transaction Safety
Never execute DELETE in production without transaction control. Transactions enable rollback if deletion affects unexpected rows.
BEGIN TRANSACTION;
-- Check row count before deletion
SELECT COUNT(*) FROM orders WHERE status = 'test';
-- Expected: 150 rows
DELETE FROM orders WHERE status = 'test';
-- Verify: (150 rows affected)
-- If count matches expectation
COMMIT;
-- If something's wrong
ROLLBACK;
For large deletions, use batching to avoid lock escalation:
-- Delete in batches of 1000
DO $$
DECLARE
deleted_count INT;
BEGIN
LOOP
DELETE FROM log_entries
WHERE log_id IN (
SELECT log_id
FROM log_entries
WHERE created_at < '2023-01-01'
LIMIT 1000
);
GET DIAGNOSTICS deleted_count = ROW_COUNT;
EXIT WHEN deleted_count = 0;
COMMIT;
PERFORM pg_sleep(0.1);
END LOOP;
END $$;
Soft Delete Pattern
Production systems typically implement soft deletes to preserve data integrity and enable recovery. This pattern uses a status column instead of physical deletion.
-- Add deleted_at column
ALTER TABLE customers
ADD COLUMN deleted_at TIMESTAMP NULL;
CREATE INDEX idx_customers_deleted ON customers(deleted_at)
WHERE deleted_at IS NULL;
-- Soft delete implementation
UPDATE customers
SET deleted_at = CURRENT_TIMESTAMP,
updated_by = 'system'
WHERE customer_id = 12345;
-- Query active records
SELECT * FROM customers WHERE deleted_at IS NULL;
-- Create view for convenience
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE deleted_at IS NULL;
-- Restore soft-deleted record
UPDATE customers
SET deleted_at = NULL
WHERE customer_id = 12345;
CASCADE and Foreign Keys
Foreign key constraints with CASCADE options automate related record deletion, but require careful design to prevent unintended data loss.
-- Define cascading relationship
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
);
CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INT NOT NULL,
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE
);
-- Single delete cascades through relationships
DELETE FROM customers WHERE customer_id = 100;
-- Automatically deletes related orders and order_items
-- Safer: use RESTRICT or SET NULL
ALTER TABLE orders
DROP CONSTRAINT orders_customer_id_fkey,
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE RESTRICT;
Performance Considerations
Large deletions impact database performance. Use EXPLAIN to understand execution plans and optimize accordingly.
-- Check execution plan
EXPLAIN DELETE FROM logs WHERE created_at < '2023-01-01';
-- Ensure indexes exist on WHERE clause columns
CREATE INDEX idx_logs_created ON logs(created_at);
-- For massive deletions, TRUNCATE is faster
TRUNCATE TABLE staging_data; -- No WHERE clause, resets table
-- Partition-based deletion (PostgreSQL)
ALTER TABLE logs DETACH PARTITION logs_2022;
DROP TABLE logs_2022;
DELETE operations generate transaction logs. For data warehouses or archival scenarios, consider INSERT-SELECT into a new table and rename:
-- More efficient for keeping small subset
CREATE TABLE orders_new AS
SELECT * FROM orders WHERE order_date >= '2023-01-01';
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
The DELETE statement remains essential for data management, but production implementations require defensive coding practices, transaction safety, and consideration of soft delete alternatives for critical business data.