SQL 27 🛢️ Deleting Data with DELETE
The DELETE statement removes rows from a table. It is the counterpart to INSERT and UPDATE, and like UPDATE it carries significant risk: an unqualified DELETE removes every row in a table, and without a transaction there is no built-in way to recover. Unlike DROP TABLE, which removes the table itself, DELETE removes data while leaving the table structure, constraints, indexes, and triggers intact. The distinction matters because DELETE is the statement used for routine data removal, while DROP and TRUNCATE are structural or bulk operations with different performance and transaction characteristics.
This chapter covers the syntax of DELETE, the role of the WHERE clause in limiting scope, deletion with subqueries and joins, the difference between DELETE, TRUNCATE, and DROP, foreign key constraint behavior, transactions and rollback, soft deletes, and the patterns that prevent accidental mass deletion.
Key point: DELETE removes rows matching a WHERE clause. Without WHERE, every row is deleted. Use a transaction to verify before committing, understand foreign key cascade behavior, and consider soft deletes when data must be recoverable.
Why DELETE exists
The data lifecycle problem. Data has a lifecycle. Orders are archived, sessions expire, temporary records are cleaned up, users request account deletion, and stale data is purged. The DELETE statement is the mechanism for removing rows that are no longer needed while preserving the table’s structure for future inserts.
The space and performance problem. Tables grow. Without deletion, a table accumulates rows indefinitely, consuming storage and slowing queries that scan it. Indexes grow proportionally. Regular deletion of obsolete data keeps tables at a manageable size and maintains query performance. Some systems use partitioning and drop entire partitions rather than deleting rows, but DELETE remains the row-level mechanism.
The compliance problem. Regulations require deletion of personal data under specific conditions. GDPR’s right to erasure requires that personal data be deleted when it is no longer necessary for the purpose it was collected. HIPAA requires disposal of protected health information. CCPA grants consumers the right to request deletion. DELETE is the technical implementation of these requirements, though it must be paired with deletion from backups and logs to be complete.
The reference integrity problem. Rows in one table are often referenced by rows in another. Deleting a parent row while child rows still reference it would violate referential integrity. Foreign key constraints either prevent the deletion or cascade it to child rows, depending on how the constraint is defined. Understanding this behavior is essential to avoid unexpected deletions or blocked operations.
The recovery problem. DELETE is destructive. Unlike UPDATE, which can be reversed by another UPDATE, DELETE removes the row entirely. Recovery requires a backup, a transaction rollback, or a soft delete pattern where rows are marked as deleted but retained. Each approach has trade-offs between storage, performance, and recoverability.
a. Basic DELETE syntax
The DELETE statement has two parts: the table and the WHERE clause that identifies which rows to remove.
DELETE FROM table_name
WHERE condition;A single-row deletion:
DELETE FROM employees
WHERE employee_id = 105;A conditional deletion affecting multiple rows:
DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;The WHERE clause is syntactically optional. Omitting it deletes every row:
-- Deletes all rows, table structure remains
DELETE FROM employees;This is valid and sometimes intentional — clearing a staging table, for example. But for any targeted deletion, the WHERE clause is mandatory in practice.
b. The WHERE clause: scope and safety
The WHERE clause in DELETE accepts the same conditions as in SELECT and UPDATE: comparison operators, BETWEEN, IN, LIKE, IS NULL, and logical operators.
DELETE FROM logs
WHERE level = 'DEBUG'
AND created_at < NOW() - INTERVAL '30 days';The recommended practice, as with UPDATE, is to run a SELECT with the same WHERE clause first:
SELECT COUNT(*) FROM logs
WHERE level = 'DEBUG'
AND created_at < NOW() - INTERVAL '30 days';This confirms how many rows will be deleted and lets the operator verify that the count matches expectations. If the count is unexpectedly large or zero, the WHERE clause is wrong.
NULL handling follows the same rules as UPDATE. WHERE column = NULL never matches; use IS NULL or IS NOT NULL.
-- Correct
DELETE FROM customers
WHERE last_login IS NULL;
-- Incorrect: never matches
DELETE FROM customers
WHERE last_login = NULL;c. DELETE with subqueries and joins
A DELETE can remove rows based on data in another table. A subquery in the WHERE clause filters rows based on related data:
DELETE FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE status = 'closed'
);Some databases support DELETE with a USING clause (PostgreSQL) or a JOIN (MySQL) for more complex conditions:
-- PostgreSQL
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id
AND c.status = 'closed';
-- MySQL
DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'closed';The subquery form is portable. The USING and JOIN forms are database-specific and often more efficient for large datasets.
d. DELETE, TRUNCATE, and DROP
These three statements are often confused because they all remove data. They differ in scope, performance, transaction behavior, and what they leave behind.
DELETE removes rows matching a WHERE clause. It fires triggers, checks foreign key constraints, writes to the transaction log for each row, and can be rolled back. It is slow for large tables because each row deletion is logged individually.
TRUNCATE removes all rows from a table in a single operation. It does not support WHERE, does not fire row-level triggers (though some databases fire statement-level triggers), and resets identity counters. It is much faster than DELETE for clearing a table because it deallocates pages rather than logging each row. In most databases, TRUNCATE is transactional — it can be rolled back — but in some (notably MySQL with InnoDB), TRUNCATE is treated as DDL and commits implicitly.
DROP removes the table itself, including its structure, indexes, constraints, and triggers. It is a DDL operation, not a DML operation, and cannot be rolled back in most databases. After DROP, the table must be recreated before it can be used.
| Statement | Removes | WHERE | Triggers | Rollback | Speed |
|---|---|---|---|---|---|
| DELETE | Rows | Yes | Row-level | Yes | Slow |
| TRUNCATE | All rows | No | Statement-level only | Depends on DB | Fast |
| DROP | Table | No | No | No | Instant |
e. Foreign key constraints and cascade behavior
When a row is deleted, rows in other tables may reference it through foreign keys. The behavior depends on how the foreign key was defined.
RESTRICT (the default in most databases) prevents the deletion if any child row references the parent. The DELETE fails with a foreign key violation.
CASCADE deletes the child rows automatically when the parent is deleted. This is powerful but dangerous: a single DELETE can propagate through multiple levels of related tables.
SET NULL sets the foreign key column in child rows to NULL. This requires the column to be nullable.
SET DEFAULT sets the foreign key column to its default value.
NO ACTION is similar to RESTRICT but the check is deferred until the end of the transaction in some databases.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id)
ON DELETE CASCADE
);With this definition, deleting a customer deletes all their orders. Without ON DELETE CASCADE, the deletion is blocked if any order references the customer. The correct choice depends on the business rule: should orders be deleted when a customer is deleted, or should the deletion be prevented?
f. Transactions and rollback
Like UPDATE, DELETE can be wrapped in a transaction to allow rollback if the result is not what was intended.
BEGIN;
DELETE FROM sessions
WHERE expires_at < NOW() - INTERVAL '7 days';
-- Check the result
SELECT COUNT(*) FROM sessions;
-- If correct:
COMMIT;
-- If not:
-- ROLLBACK;The pattern of BEGIN, delete, verify, then COMMIT or ROLLBACK is the standard safety mechanism. It is especially important for deletes that affect many rows or that touch critical data.
For very large deletions, deleting in batches within separate transactions reduces lock contention and transaction log growth:
-- Repeat until no rows are deleted
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days'
LIMIT 10000;The LIMIT clause in DELETE is supported by MySQL. PostgreSQL uses a subquery with WHERE id IN (SELECT id ... LIMIT 10000) or ctid for batching.
g. Soft deletes and archiving
Hard deletes remove rows permanently. Soft deletes mark rows as deleted without removing them, allowing recovery and preserving referential integrity.
A soft delete adds a column — typically deleted_at (timestamp) or is_deleted (boolean) — and queries filter on it:
-- Soft delete
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE user_id = 42;
-- Queries exclude soft-deleted rows
SELECT * FROM users WHERE deleted_at IS NULL;Soft deletes have trade-offs. They preserve data and allow undelete, but every query must include the deleted_at IS NULL filter, indexes must account for it, and unique constraints become complicated because a “deleted” row still occupies the unique value. Many applications use soft deletes for user-facing data and hard deletes or periodic purges for logs and transient data.
Archiving moves rows to a separate table before deletion, preserving history while keeping the active table small:
-- Move to archive
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < '2020-01-01';
-- Delete from active table
DELETE FROM orders
WHERE order_date < '2020-01-01';This pattern is common in data warehousing and in applications with retention requirements.
Complete Example Session
-- ============================================
-- PART 1: CREATE A SAMPLE TABLE
-- ============================================
CREATE TABLE sessions (
session_id INTEGER PRIMARY KEY,
user_id INTEGER,
created_at TIMESTAMP,
expires_at TIMESTAMP,
ip_address VARCHAR(45)
);
INSERT INTO sessions VALUES
(1, 101, '2026-01-01 10:00', '2026-01-01 12:00', '192.0.2.1'),
(2, 102, '2026-01-02 09:00', '2026-01-02 11:00', '192.0.2.2'),
(3, 101, '2026-01-03 14:00', '2026-01-03 16:00', '192.0.2.1'),
(4, 103, '2026-01-04 08:00', '2026-01-04 10:00', '192.0.2.3'),
(5, 102, '2026-01-05 11:00', '2026-01-05 13:00', '192.0.2.2');-- ============================================
-- PART 2: DELETE SINGLE ROW
-- ============================================
DELETE FROM sessions
WHERE session_id = 5;-- ============================================
-- PART 3: VERIFY BEFORE DELETE
-- ============================================
-- Run SELECT with same WHERE first.
SELECT session_id, user_id, expires_at
FROM sessions
WHERE expires_at < '2026-01-03 00:00';-- ============================================
-- PART 4: DELETE WITH CONDITION
-- ============================================
DELETE FROM sessions
WHERE expires_at < '2026-01-03 00:00';-- ============================================
-- PART 5: DELETE WITH IN
-- ============================================
DELETE FROM sessions
WHERE user_id IN (101, 103);-- ============================================
-- PART 6: DELETE WITH SUBQUERY
-- ============================================
-- Delete sessions for inactive users.
DELETE FROM sessions
WHERE user_id IN (
SELECT user_id FROM users WHERE active = FALSE
);-- ============================================
-- PART 7: TRANSACTION WITH ROLLBACK
-- ============================================
BEGIN;
DELETE FROM sessions
WHERE expires_at < NOW() - INTERVAL '30 days';
-- Inspect
SELECT COUNT(*) FROM sessions;
-- If correct:
COMMIT;
-- If not:
-- ROLLBACK;-- ============================================
-- PART 8: TRUNCATE FOR FULL CLEAR
-- ============================================
-- Removes all rows, resets identity, no WHERE.
TRUNCATE TABLE sessions;-- ============================================
-- PART 9: SOFT DELETE PATTERN
-- ============================================
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE user_id = 42;
SELECT * FROM users WHERE deleted_at IS NULL;-- ============================================
-- PART 10: ARCHIVE THEN DELETE
-- ============================================
-- Move old rows to archive before removing.
INSERT INTO sessions_archive
SELECT * FROM sessions
WHERE created_at < '2025-01-01';
DELETE FROM sessions
WHERE created_at < '2025-01-01';These ten parts cover the DELETE workflow: creating a table, deleting single and multiple rows, verifying scope with SELECT, using subqueries, transaction safety, TRUNCATE for full clearing, soft deletes, and archiving. Each pattern addresses a different deletion need, and the transaction example shows the safety mechanism that prevents accidental data loss.
Quick Reference
DELETE Syntax
| Clause | Purpose |
|---|---|
DELETE FROM table | Specifies the table |
WHERE condition | Limits which rows are removed |
RETURNING cols | Returns deleted rows (PostgreSQL, SQLite) |
USING / JOIN | Delete based on another table (PostgreSQL/MySQL) |
LIMIT n | Batch deletion (MySQL) |
DELETE vs TRUNCATE vs DROP
| Statement | Removes | WHERE | Rollback | Speed |
|---|---|---|---|---|
| DELETE | Rows | Yes | Yes | Slow |
| TRUNCATE | All rows | No | Database-dependent | Fast |
| DROP | Table | No | No | Instant |
Foreign Key ON DELETE Options
| Option | Behavior |
|---|---|
| RESTRICT / NO ACTION | Block deletion if child rows exist |
| CASCADE | Delete child rows automatically |
| SET NULL | Set child foreign key to NULL |
| SET DEFAULT | Set child foreign key to default |
Safety Patterns
| Pattern | Purpose |
|---|---|
| SELECT before DELETE | Verify scope with same WHERE |
| BEGIN before DELETE | Allow rollback if wrong |
| Batch with LIMIT | Reduce lock contention |
| Soft delete | Preserve data, allow undelete |
| Archive before delete | Retain history |
Best Practices
✅ Do This:
SELECT COUNT(*) FROM logs WHERE level = 'DEBUG'; -- Verify first
DELETE FROM logs WHERE level = 'DEBUG';
BEGIN; -- Transaction for safety
DELETE FROM ... ; COMMIT;
DELETE FROM sessions WHERE expires_at < NOW(); -- Correct NULL handling
DELETE ... RETURNING session_id; -- Confirm what was deleted❌ Don’t Do This:
DELETE FROM employees; -- ❌ No WHERE
DELETE FROM logs WHERE col = NULL; -- ❌ Never matches
DELETE FROM orders; -- ❌ Mass delete without backup
TRUNCATE TABLE users; -- ❌ When DELETE with WHERE was neededCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| All rows deleted | WHERE clause omitted | Run SELECT with same WHERE first |
| No rows deleted | NULL comparison with = | Use IS NULL |
| Delete blocked by foreign key | RESTRICT constraint | Delete child rows first or use CASCADE |
| Unexpected cascade deletion | ON DELETE CASCADE defined | Review constraint definitions |
| Cannot rollback | Autocommit or DDL | Wrap in transaction; avoid TRUNCATE in transaction |
| Slow deletion on large table | Row-by-row logging | Batch with LIMIT; consider TRUNCATE |
| Data permanently lost | No backup or soft delete | Backup before mass delete; use soft delete |
Real-World Examples
1. Delete by Primary Key
DELETE FROM users WHERE user_id = 42;2. Delete Expired Sessions
DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;3. Delete with IN List
DELETE FROM products WHERE category_id IN (10, 11, 12);4. Delete with Subquery
DELETE FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'closed');5. Delete with Join (MySQL)
DELETE o FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'closed';6. Delete with USING (PostgreSQL)
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.status = 'closed';7. Delete with Returning (PostgreSQL)
DELETE FROM sessions WHERE expires_at < NOW() RETURNING session_id, user_id;8. Batch Delete (MySQL)
DELETE FROM logs WHERE created_at < '2025-01-01' LIMIT 10000;9. Soft Delete
UPDATE users SET deleted_at = NOW() WHERE user_id = 42;
SELECT * FROM users WHERE deleted_at IS NULL;10. Archive Before Delete
INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < '2020-01-01';
DELETE FROM orders WHERE order_date < '2020-01-01';Visual
DELETE Statement Anatomy
┌──────────────────────────────────────────────────────────────┐
│ DELETE STATEMENT STRUCTURE │
│ │
│ DELETE FROM sessions │
│ │ │ │ │
│ │ │ └── Table to delete from │
│ │ └── Keyword │
│ │ │
│ WHERE expires_at < NOW() - INTERVAL '30 days' │
│ │ │ │
│ │ └── Which rows to delete │
│ │ │
│ └── Filter condition │
│ │
│ Without WHERE: ALL rows are deleted. │
│ With WHERE: only matching rows are deleted. │
└──────────────────────────────────────────────────────────────┘DELETE vs TRUNCATE vs DROP
┌──────────────────────────────────────────────────────────────┐
│ THREE WAYS TO REMOVE DATA │
│ │
│ DELETE FROM t WHERE id = 5; │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ Removes: matching rows │ │
│ │ Table: remains │ │
│ │ Triggers: fire │ │
│ │ Rollback: yes │ │
│ │ Speed: slow (row-level logging) │ │
│ └────────────────────────────────────────────────────────┘ │
│ │
│ TRUNCATE TABLE t; │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ Removes: all rows │ │
│ │ Table: remains (empty) │ │
│ │ Triggers: statement-level only │ │
│ │ Rollback: database-dependent │ │
│ │ Speed: fast (page deallocation) │ │
│ └────────────────────────────────────────────────────────┘ │
│ │
│ DROP TABLE t; │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ Removes: table structure and all data │ │
│ │ Table: gone │ │
│ │ Triggers: gone │ │
│ │ Rollback: no │ │
│ │ Speed: instant │ │
│ └────────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────┘Foreign Key ON DELETE Behavior
┌──────────────────────────────────────────────────────────────┐
│ DELETING A PARENT ROW WITH CHILD ROWS │
│ │
│ Parent: customers (id=1) │
│ Child: orders (customer_id=1, 3 orders) │
│ │
│ RESTRICT / NO ACTION: │
│ └── DELETE fails: foreign key violation │
│ "Cannot delete customer 1: orders still reference it" │
│ │
│ CASCADE: │
│ └── DELETE succeeds; the 3 child orders are also deleted │
│ (cascade propagates to further child tables) │
│ │
│ SET NULL: │
│ └── DELETE succeeds; orders.customer_id becomes NULL │
│ │
│ SET DEFAULT: │
│ └── DELETE succeeds; orders.customer_id set to default │
│ │
│ The constraint definition determines which happens. │
└──────────────────────────────────────────────────────────────┘Transaction Safety Pattern
┌──────────────────────────────────────────────────────────────┐
│ VERIFY BEFORE COMMIT │
│ │
│ 1. BEGIN; │
│ │ │
│ ▼ │
│ 2. SELECT COUNT(*) FROM logs │
│ WHERE created_at < '2025-01-01'; │
│ ── confirm count matches expectation │
│ │ │
│ ▼ │
│ 3. DELETE FROM logs WHERE created_at < '2025-01-01'; │
│ │ │
│ ▼ │
│ 4. SELECT COUNT(*) FROM logs; │
│ ── confirm remaining count makes sense │
│ │ │
│ ├── Correct? ──▶ 5a. COMMIT; │
│ │ │
│ └── Wrong? ──▶ 5b. ROLLBACK; │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| DELETE purpose | Remove rows from a table |
| WHERE clause | Restricts which rows are removed |
| Without WHERE | Deletes every row |
| NULL comparison | IS NULL, not = NULL |
| Subquery | Delete based on another table |
| TRUNCATE | Removes all rows, faster, no WHERE |
| DROP | Removes the table itself |
| Foreign keys | RESTRICT, CASCADE, SET NULL, SET DEFAULT |
| Transaction | BEGIN, verify, COMMIT or ROLLBACK |
| Soft delete | deleted_at column instead of removal |
| RETURNING | Returns deleted rows (PostgreSQL, SQLite) |
Key takeaways:
- WHERE determines scope. Without it, every row is deleted. Run a SELECT with the same WHERE clause first to verify which rows will be removed.
- NULL requires special handling.
WHERE column = NULLnever matches; useIS NULLorIS NOT NULL. - TRUNCATE is faster than DELETE for clearing a table. It removes all rows in one operation and does not support WHERE. Rollback behavior varies by database.
- DROP removes the table itself. It is a DDL operation, not a DML operation, and cannot be rolled back in most databases.
- Foreign key constraints control cascade behavior.
ON DELETE CASCADEdeletes child rows automatically;RESTRICTblocks the deletion. Review these definitions before deleting parent rows. - Transactions provide safety. Wrap mass deletes in
BEGIN, verify with SELECT, thenCOMMITorROLLBACK. - Soft deletes preserve recoverability. A
deleted_atcolumn marks rows as deleted without removing them, allowing undelete and maintaining referential integrity. - Archive before delete. Moving rows to an archive table preserves history while keeping the active table small.
Remember: DELETE is the statement that removes data, and once committed it is permanent unless a backup exists. The WHERE clause is the safety mechanism: it limits the deletion to the rows that should be removed. The habit of running a SELECT with the same WHERE clause before executing a DELETE is the single most effective way to prevent mistakes. For deletions that affect many rows, wrapping the operation in a transaction and verifying before committing provides a rollback path. Understand how foreign key constraints behave — a CASCADE constraint can propagate a deletion across multiple tables. When data must be recoverable, use soft deletes or archive rows before removing them. TRUNCATE and DROP serve different purposes: TRUNCATE clears a table quickly, DROP removes the table entirely. Selecting the right statement for the task is part of writing correct deletion logic.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!