DELETE in MySQL

The DELETE statement in MySQL is used to remove rows from a table based on a condition.

1. Basic DELETE Syntax

DELETE FROM table_name WHERE condition;

• WHERE is required to avoid deleting all rows.

Example: Delete an employee from IT department

DELETE FROM employees WHERE department = 'IT';

✅ Deletes all employees in the IT department.

2. DELETE ALL Rows (TRUNCATE Alternative)

DELETE FROM employees;

✅ Deletes all rows but keeps the table structure.

Alternative: Use TRUNCATE (Faster)

TRUNCATE TABLE employees;

✅ Resets the auto-increment values.
❌ Cannot use WHERE with TRUNCATE.

3. DELETE with LIMIT

To delete a specific number of rows:

DELETE FROM employees WHERE department = 'HR' LIMIT 2;

✅ Deletes only 2 employees from HR.

4. DELETE with JOIN (Deleting from Multiple Tables)

DELETE e FROM employees eJOIN departments d ON e.dept_id = d.idWHERE d.name = 'Finance';

✅ Deletes employees only from the employees table where department is Finance.

5. DELETE vs. TRUNCATE vs. DROP

6. DELETE with ORDER BY

To delete the oldest/newest records first:

DELETE FROM employees ORDER BY hire_date ASC LIMIT 5;

✅ Deletes the 5 oldest employees.

7. Best Practices

✅ Always use WHERE unless you want to delete everything.
✅ Use LIMIT when testing deletions.
✅ Backup data before deleting large records.
✅ Use TRUNCATE for faster full-table deletion.