Test accounts, expired temporary data, or records no longer needed by business rules can be removed with DELETE. It changes stored data, and successful deletion means those rows no longer exist, so it requires more care than a query.
DELETE FROM users names the target table; WHERE id = 4 selects Diana's row.
DELETE FROM users
WHERE id = 4;Forgetting WHERE removes every row
DELETE FROM users; normally leaves the users structure but empties all its data. It differs from WHERE id = 4 by one condition and has a completely different effect. Check WHERE before every DELETE.
SELECT before DELETE
Query with the same condition first. If SELECT * FROM users WHERE city = 'Tokyo'; returns Alice and Chris, the matching DELETE removes both. Fix an unexpected query before deleting.
DELETE can remove several rows
WHERE price < 20 removes every product below 20. AND, OR, IN, and BETWEEN can also filter DELETE, but destructive results deserve closer review.
Deleting rows is not deleting a table
DELETE removes data and leaves the table structure. DROP TABLE removes both structure and data and is much more destructive.
Relationships may block deletion
If orders.user_id still refers to Alice's users.id, the database may reject deletion so the order does not lose its valid user. Foreign-key and business rules determine the behavior.
Soft delete
A real “Delete account” action may set is_deleted to true or fill deleted_at. The row remains but the application hides it. This is a soft delete; a true DELETE is often called physical or hard deletion.
After deletion, query with the original condition and inspect affected rows. Empty target results plus the expected count complete the verification.
| Statement | Table structure | Rows in the table |
|---|---|---|
DELETE FROM users; | Kept | All removed |
DROP TABLE users; | Removed | Removed with the structure |
Delete one, several, or all rows
Deleted rows really disappear from the simulated table. Compare a precise WHERE, a wider condition, and no WHERE; reset restores the data.
| id | name | city | |
|---|---|---|---|
| 1 | Alice | [email protected] | Tokyo |
| 2 | Bob | [email protected] | Osaka |
| 3 | Chris | [email protected] | Tokyo |
| 4 | Diana | [email protected] | Kyoto |
Sandbox ready; no real database is connected.KNOWLEDGE QUIZ
Preview and delete target rows
Delete Electronics products priced below 50. Write a preview SELECT, the DELETE, and a verification query with the original condition. With the given data, name the removed product and expected affected-row count. Do not use DELETE without WHERE.
Optional: portable practice data
DROP TABLE IF EXISTS products;
CREATE TABLE products (id INTEGER, name VARCHAR(100), price DECIMAL(10,2), category VARCHAR(50));
INSERT INTO products VALUES
(1,'Keyboard',80.00,'Electronics'), (2,'Mouse',40.00,'Electronics'),
(3,'Coffee Mug',15.00,'Home'), (4,'Backpack',60.00,'Fashion');