DELETE Statement

Overview

The DELETE statement removes existing rows from a database table based on specified conditions.

Learning Objectives

• Delete targeted rows using DELETE FROM ... WHERE.
• Compare DELETE vs TRUNCATE vs DROP.

Detailed Concept Explanation

DELETE removes rows that match a WHERE clause:

• DELETE FROM table WHERE condition;

DELETE vs TRUNCATE vs DROP

• DELETE: DML operation. Scans and removes specified rows one by one, logging each row deletion. Slow for full tables.
• TRUNCATE: DDL operation. Quickly empties an entire table by deallocating its storage pages. Faster, but cannot target specific rows with WHERE.
• DROP: DDL operation. Deletes the entire table structure and schema from the database catalog.

Code Examples

SQL

-- Delete inactive users who registered over 5 years ago
DELETE FROM users
WHERE status = 'Inactive' 
  AND created_at < '2021-01-01';

Interview Perspective

Summary

DELETE removes specific rows matching a WHERE condition. Use TRUNCATE to wipe whole tables quickly.