How do you write a DELETE whose target rows are chosen by a match in another table?

## One target table, everything else in the WHERE clause A searched `DELETE` in standard SQL has a single target: ```sql DELETE FROM <table> [ [AS] alias ] WHERE <search condition>; ``` There is no `JOIN` in that grammar, and no way to remove rows from two tables at once. So the everyday requirement "delete the line items of cancelled orders" is expressed by putting the *other* table inside the search condition, as a subquery. ## The EXISTS form ```sql DELETE FROM order_items oi WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.id = oi.order_id AND o.status = 'CANCELLED' ); ``` This is a **correlated** subquery: it mentions `oi`, a name that belongs to the outer statement, so conceptually it is re-evaluated for each candidate row of `order_items`. `EXISTS` yields TRUE as soon as one matching row is found and FALSE otherwise — it never yields UNKNOWN — so the row is removed exactly when a cancelled parent exists. The `SELECT 1` is conventional: `EXISTS` only cares whether rows come back, never what is in them. Many engines accept an alias on the target table, as above. Where yours does not, drop the alias and qualify with the full table name (`orders.id = order_items.order_id`); the meaning is identical. ## The IN form, and why EXISTS is the safer habit ```sql DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE status = 'CANCELLED'); ``` For a single-column match this is equally correct and often more readable. Two reasons to reach for `EXISTS` by default: it extends naturally to multi-column correlations (`o.tenant_id = oi.tenant_id AND o.id = oi.order_id`), and its negation is well behaved. `NOT IN` over a subquery whose column can be NULL famously stops matching anything at all, whereas `NOT EXISTS` has no such trap — which matters, because the negated form is exactly what you write to delete orphans: ```sql DELETE FROM order_items oi WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.id = oi.order_id); ``` ## Only the target table changes The subquery **reads** the other table; it never modifies it. A statement that deletes line items leaves `orders` completely untouched, even though `orders` is named in the text. If the business operation really is "remove the order and its items", that is two statements in one transaction, children first — or a cascading referential action declared on the foreign key in the schema. A second consequence: the subquery sees the table as it is *during* the statement. Reading the same table you are deleting from is where this gets subtle, and some engines refuse it outright unless you wrap the subquery in a derived table. ## Vendor join syntax is a convenience, not the standard Several engines offer a join-shaped `DELETE` — a `USING` clause, or naming the table to purge before `FROM` in a multi-table join. These are dialect extensions with genuinely different spellings, and code written with one does not move to another engine. Reach for them when you are writing engine-specific code and you have a reason; write the `EXISTS` form when the SQL has to be portable, live in a migration, or be read by someone who works on a different database. ## Getting it right Three checks pay for themselves every time: 1. **Qualify every column** inside the subquery with its table or alias. An unqualified name that does not exist in the inner table silently resolves to the outer one, and the predicate quietly becomes true for everything. 2. **Preview the target set.** Turn the statement into `SELECT COUNT(*) FROM order_items oi WHERE EXISTS (...)` with the identical predicate and see the number before you commit to it. 3. **Decide match versus anti-match deliberately.** `EXISTS` deletes the rows that *have* a partner; `NOT EXISTS` deletes the ones that do not. Inverting that by accident is the most expensive single-keyword mistake in this pattern.