DELETE statement
The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.
Overview
DELETE is the DML statement for removing rows from a table. Its basic form:
Copy code
DELETE FROM orders WHERE status = 'cancelled';
Omitting WHERE deletes every row in the table while leaving the table's structure (columns, constraints, indexes) intact — unlike DROP TABLE, which removes the table entirely, or TRUNCATE, which is a faster bulk-delete of all rows.
Deleting based on another table
Some engines allow a USING clause to delete rows based on a join against another table or subquery, which is more efficient and readable than a correlated subquery in the WHERE clause:
Copy code
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.is_test_account = true;
DuckDB specifics
DuckDB supports DELETE FROM table [USING other_tables] WHERE condition, letting you delete rows based on the content of other tables or subqueries. DuckDB also supports a RETURNING clause that returns the deleted rows before they're removed — handy for auditing or archiving what was deleted in the same statement:
Copy code
DELETE FROM employees
WHERE termination_date < CURRENT_DATE - INTERVAL '7 years'
RETURNING employee_id, name;
A practical DuckDB detail: DELETE marks rows as deleted rather than immediately reclaiming their disk space; space is fully reclaimed on the next CHECKPOINT. For clearing an entire table quickly without a WHERE clause, TRUNCATE is functionally equivalent to an unconditional DELETE FROM in DuckDB.
Related terms
TRUNCATE removes all rows from a table in a single fast operation, without logging individual row deletions the way DELETE does.
UPDATE statement →The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.
MERGE statement →MERGE INTO is a SQL statement that inserts, updates, or deletes rows in a target table based on how they match rows from a source table, in a single atomic operation.
ALTER TABLE statement →The ALTER TABLE statement allows you to modify the structure of an existing database table without having to recreate it from scratch.
WHERE clause →The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.
CREATE TABLE statement →The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…
FAQS
DELETE (optionally with a WHERE clause) removes specific rows and can be filtered or combined with a USING join. TRUNCATE removes all rows in one operation with no WHERE clause support, and in DuckDB is essentially shorthand for an unconditional DELETE FROM.
Not immediately. DuckDB marks deleted rows as removed, but the underlying disk space is only fully reclaimed after a CHECKPOINT operation.
