How to delete rows using a join in SQL

1DELETE … WHERE EXISTS: the portable answerRecommended

Write the join condition inside a correlated subquery: for each row of orders, the subquery looks for a matching customer that is closed, and EXISTS is true when it finds one. The subquery refers to the outer table by its name, orders.customer_id, which is what ties the two together. SELECT 1 is a convention: EXISTS only checks whether a row comes back, not what it holds.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES
    (1, 'Ana', 'active'), (2, 'Ben', 'closed'), (3, 'Cy', 'active'), (4, 'Dee', 'closed');
INSERT INTO orders VALUES
    (10, 1, 40), (11, 2, 15), (12, 2, 60), (13, 3, 25), (14, 4, 90), (15, 1, 10);

-- Delete every order that belongs to a closed customer
DELETE FROM orders
WHERE EXISTS (
    SELECT 1
    FROM customers AS c
    WHERE c.id = orders.customer_id
      AND c.status = 'closed'
);

SELECT changes() AS deleted;
SELECT * FROM orders;

Ben (orders 11 and 12) and Dee (order 14) are closed, so changes() reports 3 and orders 10, 13 and 15 remain. changes() is SQLite's count of rows the last statement touched; other databases print the count after the statement themselves. This exact DELETE also ran unchanged on PostgreSQL, MySQL and SQL Server (Babelfish), deleting the same three rows, which is why it is the form to reach for first. Each order is deleted once however many rows the subquery could find: EXISTS only asks whether there is a match.

2DELETE … WHERE id IN (SELECT … JOIN …)

When the rule needs a real join, for example two tables away from the rows you delete, write the join as an ordinary SELECT that returns the keys to remove, and feed it to IN. Here the items go when their order belongs to a closed customer: the subquery joins orders to customers and returns the order ids.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER, sku TEXT);
INSERT INTO customers VALUES (1, 'Ana', 'active'), (2, 'Ben', 'closed');
INSERT INTO orders VALUES (10, 1), (11, 2), (12, 2);
INSERT INTO order_items VALUES
    (1, 10, 'pen'), (2, 11, 'ink'), (3, 11, 'pad'), (4, 12, 'pen'), (5, 10, 'cap');

-- The join lives inside the subquery: items of orders placed by closed customers
DELETE FROM order_items
WHERE order_id IN (
    SELECT o.id
    FROM orders AS o
    JOIN customers AS c ON c.id = o.customer_id
    WHERE c.status = 'closed'
);

SELECT * FROM order_items;

The subquery returns orders 11 and 12, so items 2, 3 and 4 are deleted and only Ana's two items remain. You can run the subquery on its own to see the ids first. One MySQL catch: if the subquery reads the same table you delete from, as in DELETE FROM orders WHERE id IN (SELECT o.id FROM orders AS o JOIN …), MySQL 8.4 stops with ERROR 1093 (HY000): You can't specify target table 'orders' for update in FROM clause. SQLite, PostgreSQL and SQL Server accept it. In MySQL, use EXISTS or the DELETE … JOIN form from section 7 instead.

3Delete rows that have no match: NOT EXISTS

To delete the rows that have no partner in the other table, such as customers who never ordered, negate the test: NOT EXISTS. It is tempting to write NOT IN (SELECT customer_id FROM orders) instead, but a single NULL in that column makes NOT IN match nothing at all. The example runs both against the same data. The same choice comes up when you only want to list such rows; see find rows with no match.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cy'), (4, 'Dee');
INSERT INTO orders VALUES (10, 1), (11, 3), (12, NULL);

-- NOT IN deletes nothing: the NULL in orders.customer_id makes every test unknown
DELETE FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
SELECT changes() AS not_in;

-- NOT EXISTS ignores the NULL and removes the customers with no orders
DELETE FROM customers
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = customers.id
);
SELECT changes() AS not_exists;

SELECT * FROM customers;

The NOT IN delete reports 0: for Ben, 2 NOT IN (1, 3, NULL) is unknown rather than true, because 2 might equal the unknown value, and WHERE keeps only rows that are true. NOT EXISTS deletes 2 rows, Ben and Dee, leaving Ana and Cy. If you do use NOT IN, add WHERE customer_id IS NOT NULL to the subquery.

4Preview what will be deleted, then delete

A DELETE with the wrong condition is hard to undo, so run the same WHERE clause in a SELECT first and read the rows it returns. Then copy that clause, unchanged, into the DELETE. Doing the delete inside a transaction adds a second check: compare the count with the preview, and COMMIT only if they agree.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES
    (1, 'Ana', 'active'), (2, 'Ben', 'closed'), (3, 'Cy', 'active'), (4, 'Dee', 'closed');
INSERT INTO orders VALUES
    (10, 1, 40), (11, 2, 15), (12, 2, 60), (13, 3, 25), (14, 4, 90), (15, 1, 10);

-- 1. Preview: the same WHERE clause, in a SELECT
SELECT * FROM orders
WHERE EXISTS (
    SELECT 1 FROM customers AS c
    WHERE c.id = orders.customer_id AND c.status = 'closed'
);

-- 2. Delete inside a transaction, check the count, then commit or roll back
BEGIN;
DELETE FROM orders
WHERE EXISTS (
    SELECT 1 FROM customers AS c
    WHERE c.id = orders.customer_id AND c.status = 'closed'
);
SELECT changes() AS deleted;
ROLLBACK;

SELECT COUNT(*) AS still_there FROM orders;

The preview lists orders 11, 12 and 14, and the DELETE reports 3, so the two agree. This example then rolls back to show the safety net: still_there is 6, every order is back. In real use you would write COMMIT where it says ROLLBACK. A preview written as a join (SELECT o.* FROM orders o JOIN customers c ON …) is fine for reading, but keep the delete's own WHERE clause identical to what you previewed.

5See the deleted rows with RETURNING

RETURNING at the end of a DELETE hands back each row as it is removed, so the statement reports exactly what it did, in one step. It is handy for logging, for moving rows to an archive from application code, or simply as a check. SQLite has supported it since version 3.35.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES
    (1, 'Ana', 'active'), (2, 'Ben', 'closed'), (3, 'Cy', 'active'), (4, 'Dee', 'closed');
INSERT INTO orders VALUES
    (10, 1, 40), (11, 2, 15), (12, 2, 60), (13, 3, 25), (14, 4, 90), (15, 1, 10);

-- RETURNING (SQLite 3.35+) prints each row as it is deleted
DELETE FROM orders
WHERE EXISTS (
    SELECT 1 FROM customers AS c
    WHERE c.id = orders.customer_id AND c.status = 'closed'
)
RETURNING id, customer_id, total;

The three deleted orders come back as a result set: 11, 12 and 14. PostgreSQL uses the same RETURNING clause, including after DELETE … USING. SQL Server's equivalent is OUTPUT deleted.id, deleted.total, written after the DELETE o line. MySQL 8.4 has no RETURNING on DELETE (it reports a syntax error near 'RETURNING id'), so run the preview SELECT instead.

6Why SQLite rejects DELETE … JOIN

The join syntax many answers show, DELETE o FROM orders o JOIN customers c ON …, is a MySQL and SQL Server extension. SQLite's DELETE takes exactly one table and a WHERE clause, so it fails at the first word it doesn't expect. Run it to see the error, then rewrite it with EXISTS from section 1.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Ana', 'active'), (2, 'Ben', 'closed');
INSERT INTO orders VALUES (10, 1, 40), (11, 2, 15), (12, 2, 60);

-- MySQL and SQL Server syntax: SQLite has no join in DELETE
DELETE o
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.status = 'closed';

SQLite stops with Parse error near line 7: near "o": syntax error: it expected FROM straight after DELETE. The other variants fail the same way. DELETE FROM orders AS o JOIN customers … gives near "JOIN": syntax error, and PostgreSQL's DELETE FROM orders USING customers … gives near "USING": syntax error. The fix is always the same: move the join condition into WHERE EXISTS (…) or WHERE id IN (SELECT …).

7Other databases: DELETE with a join

Each form below ran on PostgreSQL 18, MySQL 8.4 or SQL Server (through Babelfish), deleting the orders of closed customers from the tables in section 1. The two subquery forms run everywhere, including SQLite; the join forms are each tied to their database. Oracle also accepts the EXISTS and IN forms.