DELETE removes entire rows from a table. It shares the exact same danger as UPDATE: run it without a WHERE clause and every row in the table is gone, not just the ones you meant to remove.
Learning Objectives
- Write a DELETE statement that removes specific rows.
- Explain the difference between DELETE and TRUNCATE.
- Understand how deleting a row interacts with foreign key relationships.
WHERE Matches
Rows identified
DELETE FROM
Matched rows removed
Gone
Permanently, unless in a transaction
Basic DELETE
DELETE FROM names the table, WHERE narrows it down to specific rows, exactly the same condition syntax as SELECT and UPDATE. Every row matching the condition is removed entirely, not just cleared.
DELETE FROM employees WHERE department = 'Research' AND salary < 85000;
DELETE Without WHERE
Valid SQL, no confirmation, and it empties the entire table. Exactly the same risk as UPDATE without WHERE, and exactly the same discipline applies: preview with a matching SELECT first if there's any doubt.
DELETE FROM employees;
DELETE vs TRUNCATE
| DELETE | TRUNCATE | |
|---|---|---|
| Can use WHERE | Yes | No, always removes everything |
| Speed on large tables | Slower, logs each row removed | Much faster, deallocates data in bulk |
| Resets auto-increment counter | No | Yes, in most databases |
| Can be rolled back in a transaction | Yes | Depends on the database (yes in PostgreSQL/SQL Server, not in MySQL's default engine behavior) |
TRUNCATE exists specifically for "empty this entire table, fast" and has no WHERE clause at all. For removing a subset of rows, DELETE is the only option.
Foreign Keys and Deleting
If another table has a foreign key pointing at the rows you're trying to delete, for example an orders table referencing employees, the database will typically block the DELETE with a constraint error rather than leave orphaned references behind. How to handle this on purpose, cascading deletes, is covered in the Database Design module later in this course.
Frequently Asked Questions
Does DELETE FROM employees WHERE id = 5 reduce the next auto-increment value?
No. Deleting a row doesn't reset or reuse its ID, the next inserted row still gets the next number in sequence. This is intentional, reusing IDs after a delete can create confusing references to old, unrelated data.
Can I undo a DELETE?
Only if it was run inside a transaction that hasn't been committed yet (covered in a later lesson), or by restoring from a backup. Once committed outside a transaction, a DELETE is permanent, which is exactly why the WHERE clause matters so much.
What's the difference between DELETE FROM employees and DROP TABLE employees?
DELETE removes rows but keeps the table, its columns, and its constraints intact, empty but still there. DROP TABLE removes the table itself entirely, structure and all. They're not interchangeable.
Common Beginner Mistakes
Running DELETE without a WHERE clause
Exactly like UPDATE, this is valid SQL that silently empties the entire table with no confirmation. Always double-check the WHERE clause, or preview with a matching SELECT, before running a DELETE.
Reaching for TRUNCATE when only some rows need to go
TRUNCATE has no WHERE clause, it always removes everything. If you need to remove a subset of rows, DELETE with a WHERE is the only correct tool.
Assuming a DELETE blocked by a foreign key constraint is a bug
It's usually the database working as intended, protecting you from leaving other tables' rows pointing at data that no longer exists. The fix is deciding what should actually happen to those related rows, not disabling the constraint.
Best Practices
- Preview a DELETE's WHERE clause as a SELECT first, confirm the row count matches what you expect, before running the actual delete.
- Wrap any DELETE you're uncertain about in a transaction so it can be rolled back if the result looks wrong.
- Reach for TRUNCATE only when you genuinely want to empty an entire table, never as a shortcut for a conditional delete.
- Think through what should happen to related rows in other tables before deleting, don't let a foreign key error surprise you.
Interview Questions
What's the practical difference between DELETE FROM table; and TRUNCATE TABLE table;?
Both remove every row, but DELETE logs each row removal individually and can be rolled back mid-transaction, while TRUNCATE deallocates the data in bulk, is typically much faster on large tables, and resets the auto-increment counter in most databases, TRUNCATE is effectively closer to "recreate the table" than "remove each row."
Why might a DELETE fail with a foreign key constraint error, and how would you fix it?
It fails because another table has rows referencing the ones you're trying to delete, and removing them would leave a dangling reference. The fix is deciding the right behavior: delete the dependent rows first, reassign them, or configure ON DELETE CASCADE/SET NULL on the foreign key so the database handles it automatically.
Write a DELETE that removes employees who left the company (status = 'inactive') and earned under 50000.
DELETE FROM employees WHERE status = 'inactive' AND salary < 50000; — straightforward AND condition, same pattern as a SELECT's WHERE clause.
Summary
This lesson covered removing specific rows with DELETE FROM ... WHERE, the difference between DELETE and TRUNCATE, and how foreign key relationships can block a DELETE to protect data integrity.
What's Next?
The next lesson covers NULL in full depth, how it behaves in comparisons, aggregates, and sorting, and how to handle it correctly.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.