SQL DELETE Statement Explained with Examples (2026)

The statement runs, four rows come back as affected, and you meant to remove one. DELETE removes the rows its condition matches, and leaves the table, its columns and its indexes where they are:

DELETE FROM table_name
WHERE condition;

Leave the condition out and every row in the table goes. The way to be sure of a DELETE is to run its condition as a SELECT first and count what comes back.

What the SQL DELETE statement does

The statement has two parts, and only the first is required:

• DELETE FROM names the table whose rows go.
• WHERE takes a condition, and the rows for which it is true are the rows removed.

DELETE FROM orders; is therefore valid SQL. It empties the table one row at a time, firing whatever triggers the table carries, and nothing asks you to confirm first.

Examples on this page run against a pair of tables, customers and their orders:

CREATE TABLE customers (
    customer_id     INT PRIMARY KEY,
    name            VARCHAR(50),
    customer_status VARCHAR(20)
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    order_total DECIMAL(10,2),
    status      VARCHAR(20)
);

INSERT INTO customers VALUES
    (1, 'Ada',   'active'),
    (2, 'Grace', 'inactive'),
    (3, 'Linus', 'inactive');

INSERT INTO orders VALUES
    (10, 1, 40.00, 'shipped'),
    (11, 2, 12.50, 'cancelled'),
    (12, 3, 99.90, 'shipped'),
    (13, 1,  7.00, 'cancelled'),
    (14, 2, 25.00, 'cancelled');

Turn the DELETE you mean to run into a SELECT with the same FROM and the same WHERE, and read what it returns:

SELECT * FROM orders
WHERE status = 'cancelled';

Three rows, and you can see which three. That is the entire safety habit, and it costs one keystroke to swap SELECT * for DELETE. The SQL WHERE clause covers the conditions themselves.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Delete a single row

A single-row delete targets the primary key, because the primary key is the one condition that cannot match a second row by accident:

DELETE FROM orders
WHERE order_id = 12;

SELECT * FROM orders ORDER BY order_id;

Order 12 is gone and the other four rows are untouched. A filter on a column that is not unique carries a weaker guarantee. WHERE order_total = 99.90 would have removed order 12 today and any number of rows tomorrow.

Delete multiple rows

A condition that matches a group removes the group in one statement:

DELETE FROM orders
WHERE status = 'cancelled';

SELECT * FROM orders ORDER BY order_id;

The three rows the preview query listed are the three that went. Any operator a WHERE clause accepts works here as well:

• order_total BETWEEN 40 AND 50 removes the rows inside a range.
• status IN ('cancelled', 'returned') removes the rows matching any value in a list.
• order_date < DATE '2024-01-01' removes the rows older than a date.

Scale changes the shape of the problem. A delete of several million rows runs as one transaction, holds its locks until it finishes, and writes every removed row to the transaction log, so cleanup jobs are written to run in batches. MySQL 8.4 has a clause for it, LIMIT, which "specifies the maximum number of rows to be deleted", and you repeat the statement until it reports fewer rows than the limit.

DELETE FROM orders
WHERE status = 'cancelled'
LIMIT 1000;

SQL Server writes the same idea as DELETE TOP (1000) FROM orders WHERE status = 'cancelled'. PostgreSQL 17 has no such clause, and its documentation says so plainly before showing the replacement: "While there is no LIMIT clause for DELETE, it is possible to get a similar effect" by selecting a batch of row identifiers first.

WITH delete_batch AS (
    SELECT ctid
    FROM orders
    WHERE status = 'cancelled'
    ORDER BY order_id
    FOR UPDATE
    LIMIT 1000
)
DELETE FROM orders
USING delete_batch
WHERE orders.ctid = delete_batch.ctid;

Delete with subqueries and joins

The rows to remove are often chosen by a value in another table. Two orders go back in first:

INSERT INTO orders VALUES
    (11, 2, 12.50, 'cancelled'),
    (12, 3, 99.90, 'shipped');

A subquery in the WHERE clause reads the other table and hands back a list of values to match:

DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE customer_status = 'inactive'
);

SELECT * FROM orders ORDER BY order_id;

Grace and Linus are the inactive customers, so their orders 11 and 12 went, and Ada's order stayed. Write this with IN rather than NOT IN where you can. A NOT IN whose subquery returns even one NULL matches nothing at all, and the delete then silently removes zero rows, for the reason SQL NULL values explains.

The same delete can be written as a join, and the syntax is where the engines part company:

DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.customer_id
  AND c.customer_status = 'inactive';

DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_status = 'inactive';

Both remove the same two orders as the subquery above. The join form is worth the extra syntax when you need columns from the second table in the condition, and MySQL 8.4 asks for one thing in return: "You cannot use ORDER BY or LIMIT in a multiple-table DELETE", so a batched cleanup has to stay single-table.

DELETE vs TRUNCATE vs DROP

Three statements remove data, and only one of them takes a condition:

TRUNCATE is the fast way to empty a table, and PostgreSQL 17 says where the speed comes from: it "has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster", and it reclaims the disk space at once instead of leaving it for a later VACUUM.

The skipped scan is what you give up, and the price of it changes from engine to engine. DELETE leaves an identity or auto-increment counter where it is on each engine below, so the next insert continues from the highest value already used. TRUNCATE is where the answers stop agreeing:

Each column is taken from that engine's own page for the statement: PostgreSQL 17, MySQL 8.4 and SQL Server 2022. The foreign key row is the one that settles the choice: no engine here will empty a parent table while its children still point at it, so a table in the middle of a schema stays a DELETE job whatever the row count. If what you actually want is to change rows rather than remove them, the SQL UPDATE statement is the one to read next.

How to delete rows safely

A transaction turns the preview and the delete into one reversible step. Run the block, read the table, then decide:

BEGIN;

DELETE FROM orders
WHERE status = 'cancelled';

SELECT * FROM orders ORDER BY order_id;

ROLLBACK;

ROLLBACK puts every deleted row back. Swap it for COMMIT once the result looks right, and understand that the swap is the point of no return: after the commit, or on a connection in auto-commit mode where each statement commits itself, only a backup brings the rows back.

Check what references the table before deleting from the parent side. What happens to the child rows was decided when the foreign key was declared, not when you ran the delete:

• Declared plainly, the foreign key makes the database refuse to delete a customer that still has orders.
• Declared ON DELETE CASCADE, the delete goes through and takes the orders with it.
• Declared ON DELETE SET NULL, the orders survive with an empty customer_id.

Each of them is a correct behavior. The clause sits on the constraint, so the cascading one is written like this:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE CASCADE;

What Is a Foreign Key? covers the choice, and foreign keys in PostgreSQL covers the PostgreSQL syntax for it.

Keep a copy of what you removed when the rows matter. PostgreSQL 17 hands them back from the statement itself, through RETURNING:

DELETE FROM orders
WHERE order_id = 10
RETURNING *;

SQL Server does the same with OUTPUT, a clause that "returns information from, or expressions based on, each row affected", written as DELETE FROM orders OUTPUT DELETED.* WHERE order_id = 10. Copying the rows into an archive table before the delete works everywhere and is covered in SQL INSERT INTO SELECT.

Run DELETE statements in DbSchema

Two questions decide whether a delete is safe: which rows the condition matches, and which child rows the delete would reach. DbSchema answers both before the statement runs.

1. Download DbSchema and connect to the database. DbSchema reverse-engineers it into an interactive diagram where every foreign key is a line, so the tables referencing the one you are deleting from are in front of you before you write the statement.
2. Open the SQL Editor from the Editors menu, put the cursor on the SELECT, and click Execute Query to read the rows you are about to remove.
3. Move to the DELETE and click Execute Query again, then click Commit to keep the change or Rollback to undo it, which is the transaction above with buttons instead of keywords.
4. Open the Relational Data Editor, part of DbSchema Pro, on the parent table to see its children beside it. Selecting a row in the parent refilters every child pane to the rows that reference it, and the refilter cascades as many levels as the foreign keys go.

On a production database, tick Read Only Connection on the Settings tab of the connection dialog and DbSchema opens that connection in read-only mode, so the database refuses every change made through it. Keep one read-only connection for looking and a second one for the statements you intend to run.

Write the SELECT, count the rows, then change one word. Download DbSchema at https://dbschema.com/download.html, connect to your database, and run the pair in the SQL Editor with Commit and Rollback in reach. Connecting, the diagram, the SQL Editor, and Read Only Connection are in the free Community Edition.