SQL | DELETE Statement

The DELETE statement in SQL is used to remove one or more rows from a table. It's essential to use the DELETE statement with caution, as it can remove data permanently from the database.

Syntax:

DELETE FROM table_name WHERE condition;

table_name: The name of the table from which rows should be deleted.
condition: The condition(s) that must be met for rows to be deleted.

Examples:

Delete a Specific Row: To delete a specific row from a table, you would use a condition to match the row. For example, if you wanted to delete a user with the ID of 5 from the users table:
```
DELETE FROM users WHERE id = 5;
```
Delete Multiple Rows: To delete multiple rows that match a certain condition, you can use a condition that matches multiple rows. For example, to delete all users older than 60:
```
DELETE FROM users WHERE age > 60;
```
Delete All Rows from a Table: If you want to remove all rows from a table but want to keep the table structure for future use, you can use the DELETE statement without a WHERE clause. However, this is rarely recommended because you can unintentionally delete all data from a table. It's generally safer to use a WHERE clause to specify conditions.
```
DELETE FROM users;
```

Important Notes:

Performance: When deleting large numbers of rows, the DELETE statement can be slow, especially if the table has many indexes, as each index needs to be updated. In such cases, alternative methods like truncating the table or creating a new table with only the desired data and renaming it might be more efficient.
TRUNCATE vs. DELETE: The TRUNCATE statement is used to delete all rows from a table quickly. Unlike DELETE, TRUNCATE does not generate individual row delete statements, so it's faster for deleting all rows in a table. However, TRUNCATE can't delete specific rows based on a condition, as DELETE can.
Backup: Before executing a DELETE statement, especially on production databases, it's always a good practice to take a backup or ensure a recent backup is available. Mistakes can happen, and it's essential to have a way to restore data if needed.
Transactions: If you're working in a system that supports transactions (like MySQL with InnoDB or SQL Server), you can wrap your DELETE statement in a transaction. This way, if something goes wrong, you can roll back the changes.

Remember, always use the DELETE statement judiciously and ensure you have proper backups before making significant changes to the data.

How to Use DELETE in SQL:
- Description: The DELETE statement removes records from a table based on specified conditions.
- Example:
```
DELETE FROM employees;
```
Deleting Specific Records with WHERE Clause in SQL:
- Description: Deletes records that satisfy a specified condition.
- Example:
```
DELETE FROM employees
WHERE emp_id = 101;
```
Deleting All Records in a Table with DELETE in SQL:
- Description: Removes all records from a table.
- Example:
```
DELETE FROM products;
```
Using LIMIT with DELETE in SQL:
- Description: Limits the number of records to be deleted.
- Example:
```
DELETE FROM customers
WHERE registration_date < '2022-01-01'
LIMIT 10;
```
Deleting Records Based on Conditions in SQL:
- Description: Deletes records based on multiple conditions.
- Example:
```
DELETE FROM orders
WHERE order_status = 'Canceled'
AND order_date < '2022-01-01';
```
Handling Foreign Key Constraints with DELETE in SQL:
- Description: Deletes records from the child table while maintaining referential integrity.
- Example:
```
DELETE FROM employees
WHERE department_id = 5;
```

Deleting Records with JOIN in SQL:

Description: Deletes records from multiple tables using JOIN.

Example:

DELETE FROM orders
USING customers
WHERE orders.customer_id = customers.customer_id
AND customers.country = 'USA';

Deleting Records with User Input in SQL:
- Description: Deletes records based on user input parameters.
- Example:
```
DECLARE @employee_id INT = 101;
DELETE FROM employees
WHERE emp_id = @employee_id;
```

Cascading Deletes in SQL:

Description: Configures the database to automatically delete related records in child tables.

Example:

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE CASCADE;

Using DELETE with OUTPUT Clause in SQL Server:
- Description: Returns information about the deleted rows.
- Example:
```
DELETE FROM employees
OUTPUT DELETED.emp_id, DELETED.emp_name
WHERE emp_salary < 50000;
```