Beginner-Friendly Topic
Take your time - it's perfectly normal to re-read this topic 2-3 times. Try the interactive code editor below to run code yourself. Use the Q&A section to check your understanding before moving on. You've got this! 🚀
DELETE Statement
DELETE removes rows from a table permanently. Like UPDATE, always use WHERE to target specific rows. Without WHERE, all rows are deleted. Understand the difference between DELETE (removes rows) and DROP TABLE (removes the entire table).
DELETE vs TRUNCATE vs DROP
- DELETE FROM table WHERE ... - removes specific rows, can be rolled back in a transaction
- DELETE FROM table - removes ALL rows (dangerous - no WHERE!)
- TRUNCATE TABLE table - removes all rows fast, resets auto-increment, cannot roll back in some DBs
- DROP TABLE table - destroys the entire table structure + all data
- Use DELETE when you need to target specific rows with WHERE
- Use TRUNCATE to empty a whole table quickly (e.g. clearing test data)
DELETE Examples
-- Delete a specific order
DELETE FROM orders WHERE id = 3;
-- Delete all cancelled orders
DELETE FROM orders WHERE status = 'cancelled';
-- Delete users who never placed an order
DELETE FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);
-- TRUNCATE - clear all test data (fast, resets IDs)
TRUNCATE TABLE payments;
-- Note: in PostgreSQL, TRUNCATE also rolls back inside transactions
-- Soft delete pattern (better than hard delete)
-- Instead of deleting, mark as deleted:
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP DEFAULT NULL;
UPDATE users SET deleted_at = NOW() WHERE id = 5;
-- Query active users only:
SELECT * FROM users WHERE deleted_at IS NULL;
-- DROP TABLE - PERMANENT, removes structure too
-- DROP TABLE temp_data; -- use carefully!Quick Quiz
Tip
Tip
Practice DELETE Statement in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Relationships define how tables connect through foreign keys
Common Mistake
Warning
A common mistake with DELETE Statement is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of DELETE Statement from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- DELETE removes rows from a table permanently.
- DELETE FROM table WHERE ... - removes specific rows, can be rolled back in a transaction
- DELETE FROM table - removes ALL rows (dangerous - no WHERE!)
- TRUNCATE TABLE table - removes all rows fast, resets auto-increment, cannot roll back in some DBs