SQL DELETE Keyword

 The DELETE keyword in SQL is used to remove rows from a table. It is a Data Manipulation Language (DML) command and is used when you want to delete specific rows that match certain criteria.

Syntax:

DELETE FROM table_name
WHERE condition;

• table_name: The name of the table from which you want to delete the data.
• condition: A condition that determines which rows should be deleted. If no condition is specified, all rows in the table will be deleted.

Key Points:

1. Conditional Deletion: The WHERE clause is essential when using DELETE to ensure that only specific rows are deleted.
2. Without WHERE Clause: If the WHERE clause is omitted, all rows from the table will be deleted (but the structure of the table remains intact).
3. Transactions: In most databases, the DELETE operation is transaction-based. This means it can be rolled back if needed, depending on the database's transaction settings.
4. Performance: DELETE can be slow on large tables, especially if there are many rows matching the condition.

Example 1: Delete specific rows based on a condition

DELETE FROM employees
WHERE employee_id = 101;

This deletes the row where the employee_id is 101 in the employees table.

Example 2: Delete all rows from a table

DELETE FROM employees;

This deletes all rows from the employees table but does not remove the table itself.

Example 3: Delete rows based on multiple conditions

DELETE FROM employees
WHERE department = 'HR' AND salary < 30000;

This deletes all employees in the HR department who earn less than 30,000.

Caution:

• Once data is deleted with DELETE, it is typically not recoverable unless backups are in place or the database supports certain recovery methods (like transaction logs).
• You can use TRUNCATE if you want to quickly delete all rows in a table (without logging each row deletion) but be careful, as TRUNCATE is more permanent than DELETE in many databases.

Example of rollback:

If you are using a transaction-based database, you can roll back a delete operation if needed:

BEGIN TRANSACTION;

DELETE FROM employees
WHERE employee_id = 101;

-- If you realize you made a mistake, you can roll it back
ROLLBACK;

In summary, the DELETE keyword is a powerful tool for removing data from tables, but it must be used carefully, especially with the WHERE clause to avoid unintentional data loss.