DELETE

DELETE removes rows from a table. Like UPDATE, its safety depends entirely on the WHERE clause — DELETE FROM table with no WHERE removes every single row. This page covers the basic form, deleting based on a join to another table, and habits worth building to avoid deleting the wrong rows.

Basic DELETE

Removing a single order

SQL

DELETE FROM orders
WHERE order_id = 4021;

DELETE 1

DELETE without WHERE removes every row in the table

DELETE FROM orders; with no WHERE clause deletes every order — instantly, with no confirmation, and (outside of an explicit transaction you have not yet committed) no undo. This is one of the most common ways to lose production data by accident. Always include a WHERE clause unless clearing the entire table is genuinely the goal — and if it is, TRUNCATE is usually the better tool, since it is faster and makes the intent unambiguous.

DELETE ... USING

Similar to UPDATE ... FROM, PostgreSQL supports DELETE ... USING for deleting rows based on a join to another table. USING brings in the second table, and the join condition goes in WHERE alongside whatever other filtering the delete needs.

Deleting order items for cancelled orders

SQL

DELETE FROM order_items
USING orders
WHERE order_items.order_id = orders.order_id
  AND orders.status = 'cancelled';

DELETE 17

USING is a join partner, not an extra filter table by itself

As with UPDATE ... FROM, forgetting the join condition between the table being deleted from and the USING table turns the operation into a cross join — every row in the target table gets paired with every row from the USING table, which usually means far more rows get deleted than intended.

A quick look at RETURNING

DELETE can end with RETURNING to capture exactly which rows were removed, and their values, before they are gone — the only chance to see that data, since after the statement commits it no longer exists in the table.

Capturing a deleted row's data

SQL

DELETE FROM orders
WHERE order_id = 4021
RETURNING *;

 order_id | customer_id | order_date | status
----------+-------------+------------+---------
     4021 |          17 | 2026-01-14 | pending

Verify with SELECT before you DELETE

Turn the DELETE's WHERE clause into a SELECT first: SELECT * FROM orders WHERE order_id = 4021; Confirm the rows returned are exactly the ones meant to be removed, then swap SELECT * FROM for DELETE FROM using the identical WHERE clause. This one habit prevents the large majority of accidental mass deletes.

DELETE FROM table WHERE condition removes matching rows.
Omitting WHERE deletes every row in the table.
DELETE ... USING deletes rows based on a join to another table.
RETURNING captures the deleted rows' data before it is gone for good.
Rehearsing the WHERE clause as a SELECT first is the cheapest safety check available.