DELETE Command

Overview

The DELETE statement removes one or more rows from a table. A WHERE clause is used to specify which rows to delete. Without a WHERE clause, all rows in the table will be deleted.

Syntax

DELETE FROM <table_name> WHERE <condition>;

Basic Examples

Delete a Single Row by ID

Using the primary key for fast lookup (O(1) operation):

DELETE FROM users WHERE id = 5;

Delete by String Match

Remove rows where a text column matches a value:

DELETE FROM users WHERE name = 'Charlie';

Delete by Numeric Condition

Remove rows where a numeric column meets a condition:

DELETE FROM users WHERE age < 18;

Delete by Boolean Value

Remove inactive users:

DELETE FROM users WHERE active = FALSE;

WHERE Clause Details

Comparison Operators

The WHERE clause supports standard comparison operators:

• = Equal to
• > Greater than
• < Less than
• >= Greater than or equal
• <= Less than or equal

-- Delete rows where salary is above threshold
DELETE FROM employees WHERE salary > 100000;

-- Delete rows where age is less than cutoff
DELETE FROM users WHERE age < 21;

-- Delete exact match
DELETE FROM products WHERE price = 0.00;

Complex WHERE Conditions

AND Logic

Delete rows that match multiple conditions (all must be true):

DELETE FROM users WHERE age > 30 AND active = FALSE;

OR Logic

Delete rows that match at least one condition:

DELETE FROM users WHERE status = 'archived' OR age > 100;

Combined AND/OR with Grouping

Use parentheses to control logical precedence:

DELETE FROM orders WHERE (status = 'cancelled' AND created_date < '2020-01-01') 
                   OR (total = 0 AND reason = 'test');

NOT Operator

Delete rows that do NOT match a condition:

DELETE FROM users WHERE NOT (status = 'active');

NULL Checks

Delete Rows with NULL Values

DELETE FROM users WHERE profile_picture IS NULL;

Delete Rows WITHOUT NULL Values

DELETE FROM users WHERE phone_number IS NOT NULL;

Mixed NULL and Regular Conditions

DELETE FROM orders WHERE (delivery_date IS NULL) AND (status = 'pending');

Performance Notes

Primary Key Lookups (O(1))

Deleting by primary key is extremely fast:

DELETE FROM users WHERE id = 123;

Other Column Scans (O(n))

Deleting by non-indexed columns requires a full table scan:

DELETE FROM users WHERE email = 'user@example.com';

Safety Best Practices

1. Always Test First with SELECT

Before running a DELETE, verify the rows with SELECT:

-- First, verify what will be deleted
SELECT * FROM users WHERE age < 18;

-- Then, delete with confidence
DELETE FROM users WHERE age < 18;

2. Create a Backup

Backup your database before bulk deletions:

BACKUP 'before_deletion_2024';
DELETE FROM users WHERE status = 'deprecated';
-- Verify results, or restore if needed

3. Use Transaction Limits

Delete in smaller batches for large datasets:

-- Delete inactive users, but in manageable chunks
DELETE FROM users WHERE active = FALSE AND joined_date < '2020-01-01';

4. Add Comments to Risky Operations

-- CAUTION: Removing all test records from production database
-- Created backup: 'prod_backup_2024_01_15'
DELETE FROM orders WHERE order_id IN (9001, 9002, 9003);

Common Pitfalls

❌ Forgetting the WHERE Clause

This will delete every row in the table:

DELETE FROM users;  -- ALL users deleted!

❌ Incorrect Column Names

Typos in column names will cause errors:

DELETE FROM users WHERE username = 'alice';  -- Column 'username' doesn't exist

❌ Type Mismatches

Comparing incompatible types may not match rows:

DELETE FROM users WHERE id = '123';  -- id is INT, not TEXT

❌ Complex Conditions Without Testing

Run SELECT first to verify the condition logic:

-- Test the condition
SELECT COUNT(*) FROM users WHERE (age > 50 AND status = 'inactive') OR premium = FALSE;

-- Then delete
DELETE FROM users WHERE (age > 50 AND status = 'inactive') OR premium = FALSE;

DELETE vs Other DML Commands