DELETE

Hook: Interviewers love DELETE because one missing WHERE can erase a whole table in a single breath.

Question: What does DELETE do in SQL?

Answer: DELETE removes rows from a table. It does not remove the table structure itself, and it is usually paired with a WHERE clause so you remove only the rows you intend. If you leave off WHERE, every row in the table is targeted.

Interview-Ready Answer: I use DELETE when I want to remove specific rows from a table. It is a DML statement, meaning it changes data rather than schema, and I almost always write a WHERE clause so I do not wipe the whole table by accident. In most databases it is transactional, so if I catch a mistake before COMMIT, I can roll it back.

What DELETE Means

DELETE is a data change command: it removes rows, not the table itself. That makes it different from DROP, which removes the whole object, and from TRUNCATE, which empties the table much more aggressively.

How It Works Under the Hood

1. The database parses the statement and figures out which table and predicate (the filter condition) you mean.
2. It finds matching rows. If the WHERE clause can use an index, the engine can jump to the rows quickly; if not, it may scan the whole table.
3. It locks or marks those rows so concurrent transactions do not corrupt the result. In many modern engines that use MVCC, which means multi-version concurrency control, readers keep seeing a stable snapshot while the delete is being prepared.
4. It records the change in the transaction log so the operation can be committed, rolled back, and recovered after a crash.
5. If the table has foreign keys, triggers, or cascading rules, the database checks them before the delete is finalized.
6. On commit, the rows are no longer visible to future reads. Physically, some engines remove the storage immediately; others clean up later during background vacuum or space reclamation.

When and Why to Use It

• Use DELETE when you need to remove a small or medium set of rows with business rules, like expired sessions or canceled orders.
• Use it when you need safety and control. The WHERE clause lets you target exactly the right rows.
• Use it inside a transaction when you want the option to review, test, and roll back before commit.

DELETE vs TRUNCATE vs DROP

Memory rule: DELETE is the scalpel, TRUNCATE is the drain plug, and DROP is throwing away the whole sink.

Performance and Complexity

If the filter matches k rows, the work is roughly proportional to those rows plus any index maintenance. With a good index, deleting 10 rows from a 10 million row table can be fast; without an index, the engine may scan all 10 million rows first, which is much slower. Large deletes can also create heavy log traffic and replication lag, so teams often delete in batches of 1,000 to 10,000 rows at a time.

Important Edge Cases

• No WHERE clause: every row is targeted. This is legal, but dangerous.
• Foreign keys: the delete may fail if child rows still reference the row, unless cascading is configured.
• Triggers: a delete can fire audit or cleanup logic, so one statement may do more than just remove data.
• Zero rows affected: that is not an error; it usually means the filter matched nothing.
• Huge tables: long transactions can hold locks too long and slow down other users.

Real-World Story: Imagine a checkout service for an e-commerce site that stores temporary inventory reservations. A nightly cleanup job is supposed to remove only expired holds with DELETE so stock can be released back to shoppers. One developer accidentally ships DELETE FROM reservations; instead of filtering by expiration time. The job wipes every reservation, including active carts, and the next morning customers see items disappear from checkout or fail with mysterious stock errors.

What does the incident look like? Support tickets spike, logs show an unexpectedly large deleted-row count, and the database replication stream gets behind because one huge transaction floods the log. The team fixes it by restoring from backup or replaying from audit logs, then adds a safer pattern: verify the SELECT first, delete in small batches, and always review the WHERE clause before production execution.

Follow-up & Tricky Questions:

• What is the difference between DELETE and TRUNCATE? DELETE removes selected rows and can use WHERE; TRUNCATE removes all rows at once and usually skips row-by-row filtering. In many databases, TRUNCATE is faster, but it is less flexible.
• Can DELETE be rolled back? Usually yes if it happens inside an open transaction and you have not committed yet. That is one reason DELETE is safer than many people think, as long as transaction boundaries are respected.
• What happens if I run DELETE without WHERE? Every row in the table is a target. This is legal SQL, but it is the classic foot-gun, so interviewers want to hear that you would use it only when you truly intend to empty the table.
• Does DELETE affect foreign keys or triggers? Yes. A foreign key can block the delete or cascade it to child rows, and triggers can run extra logic like audit logging or cache cleanup.
• How do I delete rows based on another table? You usually use a subquery or a join-based pattern, depending on the database. The goal is still the same: make the predicate precise so only the intended rows disappear.
• Does DELETE remove the table definition? No. It removes rows only; the columns, indexes, and other schema objects stay in place.
• Is DELETE always slow? No. Small targeted deletes can be fast, especially with an index on the filter column. It gets expensive when the database must scan many rows or update many indexes.
• Gotcha: does a successful delete mean the space is immediately reclaimed? Not always. Some engines mark rows as deleted first and clean up storage later, so disk usage may not shrink right away.

Common Mistakes:

• Forgetting WHERE: the fix is to always write the filter first, then run a SELECT with the same condition before deleting.
• Thinking DELETE removes a table: it removes rows only; use DROP TABLE if you want the table object gone.
• Ignoring foreign keys: a delete can fail or cascade into child tables, so check relationships before running it in production.
• Using one giant delete on huge data: break it into batches so you reduce lock time, log spikes, and replication lag.

Memory Hook: DELETE is the scissors: it cuts out rows you point at, but it does not tear up the whole folder. TRUNCATE empties the folder, and DROP throws the folder away.

Cheat Sheet:

• DELETE removes rows, not the table.
• WHERE controls which rows are targeted.
• No WHERE means all rows.
• It is usually transactional and can often be rolled back before commit.
• Indexes make targeted deletes faster; full scans make them slower.
• Foreign keys, triggers, and cascades can change the outcome.

Practice Tasks:

• Write a DELETE that removes all rows with status = 'inactive' from a sample table.
• Run the same delete with a condition that matches nothing and confirm that zero rows are affected.
• Compare the behavior of DELETE, TRUNCATE, and DROP on a small test table.