The DELETE query in SQL is used to remove existing records from a database table. It is a powerful command, but if used incorrectly, it can delete important data permanently. That’s why understanding its syntax, conditions, and safe usage is very important for every SQL learner and developer.
In this article, I will explain the DELETE query in SQL in a simple, practical way, with clear examples and best practices.
Example Table We will Use in This Article
Let’s assume we have this users table:
| user_id | name | status | last_login |
| 1 | Rahul | active | 2024-01-12 |
| 2 | Aman | inactive | 2021-11-10 |
| 3 | Neha | inactive | 2020-05-22 |
| 4 | Pooja | active | 2023-09-01 |
We will use this table in our examples.
DELETE Query in SQL Without WHERE Clause
DELETE FROM users;
What happens?
| user_id | name | status | last_login |
| ❌ | ❌ | ❌ | ❌ |
👉 All rows are deleted
👉 Table structure still exists
⚠️ So the advice is, this is very dangerous, and we should avoid using it on live databases.
DELETE Query in SQL with WHERE Clause
DELETE FROM users
WHERE user_id = 2;
Before DELETE
| user_id | name | status |
| 2 | Aman | inactive |
After DELETE
| user_id | name | status |
| ❌ | ❌ | ❌ |
Other records stay safe, only the record will be delete which have user_id 2.
This one is the most recommended way to use the delete query in SQL.
Deleting Multiple Rows Using a Condition
DELETE FROM users
WHERE status = 'inactive';
Before DELETE
| user_id | name | status |
| 2 | Aman | inactive |
| 3 | Neha | inactive |
After DELETE
| user_id | name | status |
| ❌ | ❌ | ❌ |
Only inactive users are removed.
DELETE Query Using AND Condition
DELETE FROM users
WHERE status = 'inactive'
AND last_login < '2022-01-01';
Matching Records
| user_id | name | status | last_login |
| 2 | Aman | inactive | 2021-11-10 |
| 3 | Neha | inactive | 2020-05-22 |
After DELETE
Both records are deleted because both conditions are true.
DELETE Query Using OR Condition
DELETE FROM users
WHERE status = 'inactive'
OR last_login < '2021-01-01';
Explanation
- Deletes users who are inactive
- OR users who haven’t logged in for a long time
OR condition is broader, so use it carefully.
DELETE Query with LIMIT (MySQL)
Assume a logs table:
| log_id | message | created_at |
| 1 | Error found | 2020-01-01 |
| 2 | Warning | 2020-01-02 |
| 3 | Info message | 2020-01-03 |
DELETE FROM logs
ORDER BY created_at
LIMIT 1;
Result
| log_id | message |
| 2 | Warning |
| 3 | Info message |
Only the oldest log is deleted.
Safe DELETE – Selecting Before DELETE
SELECT * FROM users
WHERE status = 'inactive';
Output
| user_id | name | status |
| 2 | Aman | inactive |
| 3 | Neha | inactive |
If this looks correct, then run:
DELETE FROM users
WHERE status = 'inactive';
This habit saves developers from big mistakes.
DELETE vs TRUNCATE (With Example)
| Feature | DELETE | TRUNCATE |
| Uses WHERE | Yes | No |
| Rollback | Yes | No |
| Control | High | Low |
TRUNCATE TABLE users;
👉 This deletes everything instantly. No undo.
Common Beginner Mistake (Very Important)
❌ Running this directly on production:
DELETE FROM users;
✅ Correct approach:
SELECT * FROM users WHERE status='inactive';
DELETE FROM users WHERE status='inactive';
Bottom Line
The delete query in SQL is simple in syntax but powerful in impact. When used correctly with proper conditions and safety checks, it helps maintain clean and accurate data. When used carelessly, it can cause serious data loss.
👉 Key takeaway: – Always think before you DELETE. Hope you will like it.
Read Also – UPDATE Query in SQL
