SQL DELETE – Removing Rows from Tables

Basic DELETE Syntax

SQL

DELETE FROM table_name
WHERE  condition;   -- ⚠️ without WHERE, ALL rows are deleted!

SQL

-- Delete a specific order
DELETE FROM orders
WHERE id = 1042;

-- Delete all cancelled orders older than 1 year
DELETE FROM orders
WHERE status      = 'cancelled'
  AND created_at  < NOW() - INTERVAL '1 year';

-- Delete duplicate rows keeping the lowest id
DELETE FROM contacts
WHERE id NOT IN (
    SELECT MIN(id)
    FROM contacts
    GROUP BY email       -- keep one row per email
);

⚠️

DELETE without WHERE deletes ALL rows

There is no confirmation prompt. The rows are gone immediately (unless wrapped in a transaction). Always run the matching SELECT first: replace DELETE FROM orders with SELECT COUNT(*) FROM orders to see how many rows would be deleted.

DELETE with Subquery

Use a subquery to target rows based on data in another table:

SQL

-- Delete users who have been inactive for over 2 years
DELETE FROM users
WHERE id IN (
    SELECT user_id
    FROM user_activity
    GROUP BY user_id
    HAVING MAX(last_seen) < NOW() - INTERVAL '2 years'
);

-- Delete orders for users who have been banned
DELETE FROM orders
WHERE customer_id IN (
    SELECT id FROM users WHERE status = 'banned'
);

-- PostgreSQL: DELETE with USING (more readable than subquery)
DELETE FROM orders o
USING users u
WHERE o.customer_id = u.id
  AND u.status = 'banned';

TRUNCATE vs DELETE

TRUNCATE removes all rows from a table, but behaves differently from a DELETE without WHERE:

Feature	DELETE (no WHERE)	TRUNCATE
Removes all rows?	Yes	Yes
Speed	Slow (row-by-row log)	Very fast (deallocates pages)
Transaction safe?	Yes (can ROLLBACK)	Depends on DB (not in MySQL)
Resets auto-increment?	No	Yes (in most databases)
Fires triggers?	Yes	No (in most databases)
WHERE clause?	Yes	No
Respects foreign keys?	Yes	Varies (error if referenced)

SQL

-- TRUNCATE: fastest way to empty a table (no row-by-row logging)
TRUNCATE TABLE session_logs;

-- TRUNCATE with CASCADE (PostgreSQL): also truncates referencing tables
TRUNCATE TABLE users CASCADE;

-- DELETE: slower but transactional and supports WHERE
DELETE FROM session_logs WHERE created_at < '2024-01-01';

Soft Delete Pattern

Instead of truly deleting rows, many applications mark them as deleted. This preserves history, allows undo, and simplifies auditing:

SQL

-- Add a deleted_at column to the table
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- Soft delete: set the timestamp instead of deleting the row
UPDATE users
SET    deleted_at = NOW()
WHERE  id = 42;

-- Query: always filter out soft-deleted rows
SELECT id, name, email
FROM   users
WHERE  deleted_at IS NULL;   -- active users only

-- Restore a soft-deleted row
UPDATE users
SET    deleted_at = NULL
WHERE  id = 42;

💡

Soft delete trade-offs

Benefits: auditable, recoverable, no referential integrity issues. Drawbacks: every query needs WHERE deleted_at IS NULL (partial indexes help), unique constraints may conflict (a new user with the same email as a soft-deleted user). Most ORMs and application frameworks provide built-in soft delete support.

Safety Practices

SQL

-- Safe delete workflow
BEGIN;

-- Step 1: verify which rows will be affected
SELECT id, status, created_at
FROM orders
WHERE status = 'cancelled' AND created_at < '2023-01-01';

-- Step 2: confirm the count
SELECT COUNT(*) FROM orders
WHERE status = 'cancelled' AND created_at < '2023-01-01';

-- Step 3: run the delete
DELETE FROM orders
WHERE status = 'cancelled' AND created_at < '2023-01-01';

-- Step 4: check rows affected, commit if correct
COMMIT;
-- Or: ROLLBACK;

Summary

DELETE vs TRUNCATE — and the Missing WHERE

Like UPDATE, the danger with DELETE is a forgotten WHERE: DELETE FROM orders; removes every row. Always write and verify the WHERE first.

-- ⚠️ deletes ALL orders
DELETE FROM orders;

-- ✅ scoped
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2025-01-01';

	`DELETE`	`TRUNCATE`
Removes	rows matching WHERE	all rows (no WHERE)
Speed on full table	slow (row by row, logged)	very fast
Fires triggers	yes	no
Resets identity/auto id	no	yes
Rollback-able	yes	yes in Postgres (transactional DDL)

Foreign keys: deleting a parent row referenced by children fails unless the FK has ON DELETE CASCADE (auto-deletes children) or SET NULL. Know which behavior your schema has before deleting parents.

Soft delete pattern: many apps never physically delete — they set a deleted_at timestamp or active = false flag and filter it out in queries. This keeps history, supports "undo," and avoids breaking references. Reach for real DELETE/TRUNCATE for genuinely disposable data; prefer soft delete for anything you might need to recover or audit.

🏋️ Practical Exercise

Delete one row with a WHERE condition.
Delete rows matching a range.
Count the rows before deleting them.
Compare DELETE with TRUNCATE.
Verify the result with a SELECT.

🔥 Challenge Exercise

Delete all orders older than 2020 from an orders table. Explain the difference between DELETE, TRUNCATE, and DROP, and why a missing WHERE on DELETE wipes the whole table.

📋 Summary

DELETE FROM table WHERE condition — always include the WHERE clause.
Use subqueries or JOINs (PostgreSQL: USING) to delete based on data in other tables.
TRUNCATE is faster for emptying entire tables but resets auto-increment and may not be rollback-safe in all databases.
Soft delete: add a deleted_at column and UPDATE instead of DELETE — preserves history and allows recovery.
DANGER: DELETE without WHERE removes all rows. Always test with SELECT and use transactions.

Interview Questions

What does DELETE do?
What is the difference between DELETE, TRUNCATE, and DROP?
Why is a WHERE clause important in DELETE?
Can a DELETE be rolled back?
Which is faster for emptying a table, DELETE or TRUNCATE?

FAQ

Can I DELETE rows that are referenced by foreign keys? +

Not directly — the database will raise a foreign key violation error. You must first delete or update the referencing rows. Or define the foreign key with ON DELETE CASCADE so the database automatically deletes child rows. Or use ON DELETE SET NULL to null out the foreign key column in child rows. Choose based on your data integrity requirements.

How do I delete duplicate rows keeping just one? +

The standard pattern uses a subquery to identify the row to keep (e.g., MIN(id) per group), then delete all others: DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY email). In PostgreSQL you can also use a CTE with ROW_NUMBER() to mark duplicates, then delete those with rn > 1.

Is TRUNCATE faster than DELETE for emptying large tables? +

Yes, dramatically faster. DELETE logs each row removal individually (for MVCC/WAL), so deleting 10 million rows generates 10 million log entries. TRUNCATE deallocates data pages at the storage level — it is an O(1) operation regardless of table size. For emptying whole tables use TRUNCATE; for conditional row removal use DELETE.