Everything need to know about How to Delete records from SQL table

by

The delete statement in SQL is used to remove existing records from a table, you can delete specific row based on a condition.

How to Delete records from SQL table

Basic Syntax

DELETE FROM table_name
WHERE condition;
  • table_name – The name of table where you want to delete data.
  • WHERE condition – Specifies which rows to delete. If omitted, all rows will be deleted.

Deleting specific rows

To delete specific records, you need to define a condition in where clause.

DELETE FROM employees
WHERE employee_id = 101;

This will delete the employee with employee_id = 101.

Deleting multiple rows

You can delete multiple rows by specifying a condition that matches multiple records.

DELETE FROM employees
WHERE department = 'Sales';

This will delete all employees in “Sales” department.

Deleting all rows

To delete all records from table we can use eighter DELETE or TRUNCATE. Both will remove the records from table, but as DELETE removes records from table one by one it is slow, and TRUNCATE is faster for this operation.

DELETE FROM employees;
TRUNCATE TABLE employees;

Key difference between DELETE and TRUNCATE

FeatureDELETETRUNCATE
Can use WHERE?✅ Yes❌ No
Faster for large tables?❌ No✅ Yes
Generates transaction logs?✅ Yes❌ No
Can be rolled back?✅ Yes (if within a transaction)❌ No (usually irreversible)

DELETE with JOIN

You can also delete rows based on conditions from another table using JOIN.

DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.status = 'Inactive';

This deletes the orders for customers who are inactive.

DELETE with RETURNING clause

Some databases allow to use RETURN clause, which will return the deleted rows.

DELETE FROM employees
WHERE department = 'HR'
RRETURINING *;

This will delete the employees in HR department and return the deleted rows.

DELETE with LIMIT

We can also define to delete only a limited number of records

DELETE FORM employees
WHERE department = 'Sales'
LIMIT 5;

This will delete only 5 employees from Sales department.

DELETE inside a Transaction (ROLLBACK SUPPORT)

START TRANSACTION;

DELETE FROM employees
WHERE department = 'IT';

ROLLBACK;
COMMIT;

Best Practice

✅ Always use a WHERE clause (unless deleting all rows intentionally).
✅ Use transactions for safety when deleting important data.
✅ Check affected rows using SELECT before deleting.
✅ Use TRUNCATE instead of DELETE for performance when removing all rows.
✅ Backup your data before running DELETE on critical tables.

For More SQL Interview Question Follow Us On:

Jobs In Data (Science | Analysis | Engineering )
LinkedIn Page For Data Job Openings

Thank you for reading! We hope this article has been helpful in understanding about DELETE in SQL. If you found it valuable, please share it with your network.

Also Read:-

Spread the love