beginner
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
DELETEvsTRUNCATEvsDROP.
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 withWHERE.DROP: DDL operation. Deletes the entire table structure and schema from the database catalog.
Code Examples
SQL
sql
-- Delete inactive users who registered over 5 years ago
DELETE FROM users
WHERE status = 'Inactive'
AND created_at < '2021-01-01';
Interview Perspective
Note
Interview Question: Compare DELETE, TRUNCATE, and DROP.
DELETE: Removes specific rows (DML, log-heavy, supportsWHERE).TRUNCATE: Removes all rows (DDL, fast page deallocation, cannot useWHERE).DROP: Removes rows AND the table structure itself from the database.
Summary
DELETE removes specific rows matching a WHERE condition. Use TRUNCATE to wipe whole tables quickly.
DPA
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.