SQL Delete

The SQL DELETE statement is a Data Manipulation Language (DML) command used to remove existing records (rows) from a database table. It allows you to target specific rows based on criteria or clear all data while preserving the table's structure, constraints, and indexes.

Basic Syntax

The foundational structure of a DELETE statement relies heavily on the optional WHERE clause.

DELETE FROM table_name
WHERE condition;

⚠️ CRITICAL WARNING: Always be cautious when executing a DELETE statement. If you omit the WHERE clause, all records in the table will be deleted, leaving you with an empty table layout!

Common Scenarios & Examples

Delete a Single Row

To target one specific record, filter using a unique identifier such as a Primary Key (e.g., EmployeeID).

DELETE FROM Employees
WHERE Employee
ID = 5;

Delete Multiple Rows

You can provide a condition that captures a group of records matching specific criteria.

DELETE FROM Employees
WHERE Department = 'Marketing';

Alternatively, use operators like IN to clear out specific targets:

DELETE FROM Employees
WHERE Employee
ID IN (101, 102, 108);

Delete All Rows

If you want to wipe the table completely clean but keep the table column structure intact for future use: [3]

DELETE FROM Employees;

Safer Alternatives: "Soft Deletes"

In modern production applications, permanently destroying data using DELETE is often discouraged. Instead, developers use a Soft Delete strategy by adding an is_deleted or status column to the table.

-- Instead of deleting the row, you simply flag it as hidden
UPDATE customers
SET status = 'Archived'
WHERE customer_id = 104;

Best Practices for Safe Deletion

Test with SELECT First: Before running a destructive DELETE statement, substitute DELETE with SELECT * using the exact same WHERE clause. This allows you to verify exactly which rows will be affected.
Use Transactions: Since DELETE is a DML operation, you can wrap it inside a transaction block. If something goes wrong or you make a mistake, you can use the ROLLBACK command to recover the data.

BEGIN TRANSACTION;
DELETE FROM Employees WHERE Performance
Rating = 'Poor';
-- Check your data. If it looks correct:
COMMIT;
-- If you made a mistake:
ROLLBACK;

Direct Comparison: DELETE vs. TRUNCATE vs. DROP

When managing data removal, SQL provides three distinct commands. Choosing the correct one depends on your goal and data size.

Feature	DELETE	TRUNCATE	DROP
Command Type	DML (Data Manipulation)	DDL (Data Definition)	DDL (Data Definition)
Granularity	Can delete specific rows (using WHERE) or all rows.	Deletes all rows instantly.	Deletes the entire table structure and data.
Speed / Log	Slower. Logs every row deletion individually.	Very Fast. Minimally logs data page deallocations.	Instantaneous. Removes object from schema.
Rollback	Possible if executed within a transaction.	Harder / No (depends on DBMS configuration).	No (Permanent structural deletion).
Triggers	Fires delete triggers.	Does not fire table triggers.	Does not fire table triggers.

Deleting All Rows (DELETE vs TRUNCATE)

If your absolute goal is to wipe an entire table clean, you have two options:

Option A:

DELETE FROM table_name;
Deletes rows one by one.Logs each individual row deletion in the database transaction log (making it slower on massive tables).
Does not reset auto-incrementing identity keys (the next row inserted will continue where the old count left off).

Option B:

*TRUNCATE TABLE table_name;
*Instantly drops the entire internal data storage layer and creates a fresh empty sheet.
*Much faster because it bypasses individual row logging.Resets auto-incrementing counters back to 1.

Pro-Tip: The Preview CheckBefore executing any permanent delete statement, run a SELECT query using the exact same WHERE filter. This lets you preview the exact list of targets you are about to erase.

-- Step 1: Run this preview check to verify the rows
SELECT * FROM customers WHERE account_age_years > 5;

-- Step 2: Once verified, safely change SELECT * to DELETE
DELETE FROM customers WHERE account_age_years > 5;