How Does DELETE Remove Rows?

A DELETE with a WHERE clause removes every row that matches it, one statement at a time, and it is fully transactional — you can roll it back. A DELETE without a WHERE clause removes every row in the table. The table itself, its columns, its indexes and its constraints all survive; only the contents go.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO orders (id, customer_id, amount, status) VALUES
  (1, 1, 120.00, 'shipped'),
  (2, 1, 340.50, 'pending'),
  (3, 2,  75.25, 'shipped'),
  (4, 2,  15.00, 'cancelled'),
  (5, 3,  60.00, 'cancelled');

DELETE FROM orders WHERE status = 'cancelled';

SELECT COUNT(*) AS remaining_orders FROM orders;
SELECT id, amount, status FROM orders ORDER BY id;

Three rows remain, and the two cancelled orders are gone. The table still exists with its primary key and its columns intact.

That last point distinguishes DELETE from DROP and from TRUNCATE. DROP removes the table definition itself, so any code or report referencing it breaks immediately and you have to recreate the schema. TRUNCATE — where it exists — removes every row in one operation without firing row-level triggers and, in MySQL, resets the auto-increment counter. DELETE removes rows only, and leaves the counter alone.

There is a performance dimension too. On most engines DELETE processes rows individually, writing a log entry per row and firing any delete triggers, while TRUNCATE is a single metadata operation. On a table with tens of millions of rows that is the difference between seconds and hours.

Two behaviours worth knowing. Deleting every row leaves the table allocated and empty, whereas truncating it typically reclaims the space. And a DELETE that removes rows may leave the table with gaps in its row identifiers, because the space is kept for reuse rather than returned.

And the safety point again, because it is the one that matters: a DELETE with a mistyped or missing WHERE clause is one of the most expensive mistakes you can make with a database. It is transactional, so a rollback will save you if you act quickly — but on a large table, undoing millions of deleted rows can take a long time, and every row that was deleted was a row somebody needed. Check the row count before you commit.

Filter on the status, then read the affected-row count straight after the statement. SQLite's changes() gives you the number of rows the last DELETE affected.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO orders (id, customer_id, amount, status) VALUES
  (1, 1, 120.00, 'shipped'),
  (2, 1, 340.50, 'pending'),
  (3, 2,  75.25, 'shipped'),
  (4, 2,  15.00, 'cancelled'),
  (5, 3,  60.00, 'cancelled'),
  (6, 3, 480.00, 'cancelled');

DELETE FROM orders WHERE status = 'cancelled';

SELECT changes() AS rows_deleted;

SELECT id, status FROM orders ORDER BY id;

The first result is 3 — three cancelled orders removed. The second shows the three surviving orders, none of which is cancelled.

The count is the important part of this pattern. A delete that was supposed to remove one stale row and removed three thousand is a signal that the predicate is broader than intended, and reading the count immediately tells you so while you can still roll back.

To make that roll back possible, wrap the DELETE in a transaction and check the count before committing:

BEGIN; then the DELETE, then SELECT changes(), and COMMIT only if the number is the one you expected. If it is not, ROLLBACK. On a busy table, add the tenant or account predicate to the WHERE clause as well, so the delete cannot reach rows outside the scope you intended.

Two more practical points. In most engines, a DELETE fires delete triggers for each row, so a trigger that writes to an audit table will have written one audit row per deleted row — and if you then roll back, those audit rows roll back with it. And a soft delete is often the safer production choice: add a deleted_at column, set it with an UPDATE, and filter on it everywhere. The rows stay, the data is recoverable, and the audit trail is explicit. A hard DELETE is for data you are certain you will never want again.