A DELETE with an IN-subquery emptied the whole table instead of a few rows — what went wrong?
answer
- Plausible SQL, catastrophic row count
- Unqualified column inside the subquery
- Inner scope first, then outward
- The row is compared with itself
- Qualify names to turn silence into an error
basics
~20 sAlmost always an unqualified column inside the subquery that does not exist in the subquery's table: name resolution reaches outward and binds it to the table being deleted, so the predicate compares each row with itself and is true for every row.
solid answer
~50 sThe classic cause is silent correlation. In `DELETE FROM archive WHERE order_id IN (SELECT order_id FROM staging)`, if `staging` has no `order_id` column — it is called `id`, or a migration renamed it — the name is not an error. SQL resolves an unqualified column in the innermost scope where it exists and otherwise **reaches outward**, so it binds to `archive.order_id`. The subquery now returns the current outer row's own value once per staging row, the predicate is TRUE for every row with a non-NULL `order_id`, and the table empties. The defences are cheap and habitual: alias both tables and qualify every column, so a wrong name becomes an error instead of a silent match; prefer an explicit `EXISTS` with a visible correlation predicate; run the identical `FROM`/`WHERE` as `SELECT COUNT(*)` first; and execute destructive statements inside an explicit transaction, checking the affected-row count before `COMMIT`.
code
sql · 8 lines-- staging has no order_id column: the name resolves outward to archive.order_id,
-- the predicate is true for every non-NULL row, and the table empties
DELETE FROM archive
WHERE order_id IN (SELECT order_id FROM staging);
-- qualified: a wrong column name is now an error, not a silent match
DELETE FROM archive a
WHERE EXISTS (SELECT 1 FROM staging s WHERE s.order_id = a.order_id);go deeper
Know that a column name inside a subquery may belong to the outer table, and get into the habit of prefixing every column with its table alias.
Explain the resolution rule — innermost scope first, then outward — and show why the resulting predicate compares a row with itself and is therefore true for nearly every row.
Diagnose it from the symptom alone, rewrite to a qualified EXISTS, and demonstrate the operating discipline: counted preview, explicit transaction, row-count check before commit.
Address it as a process defect, not a typo: how destructive DML reaches production at all, what review and rehearsal it must pass, and what the recovery objective is when a committed delete cannot be undone.
## The symptom A cleanup statement that was supposed to remove a few thousand rows reports millions, or empties the table outright. The SQL looks unremarkable: ```sql DELETE FROM archive WHERE order_id IN (SELECT order_id FROM staging); ``` No syntax error, no warning, no constraint violation. That combination — plausible SQL, catastrophic row count — points at one bug far more often than any other. ## Name resolution reaches outward SQL resolves an unqualified column reference by looking in the innermost query scope first; if the name is not found there, it looks in the enclosing scope, and so on outward. That rule is what makes correlated subqueries possible at all — it is how `WHERE o.id = oi.order_id` inside an `EXISTS` can mention the outer row. The hazard is that the rule fires whether or not you meant it. If `staging` has no column called `order_id`, the reference in `SELECT order_id FROM staging` does not fail. It resolves outward to `archive.order_id`, and the subquery silently becomes: ```sql SELECT archive.order_id FROM staging -- one copy of the outer row's own value per staging row ``` For each candidate row of `archive`, that subquery returns that row's own `order_id`, repeated once per row in `staging`. The predicate `order_id IN (its own value)` is TRUE. So every `archive` row with a non-NULL `order_id` is deleted, as long as `staging` contains at least one row. Rows where `order_id` is NULL survive, because `NULL IN (NULL)` is UNKNOWN rather than TRUE — a detail that makes the aftermath even more confusing, since a few rows are left behind. A related variant: the column exists in *both* tables, and you correlated on the wrong one, so the subquery is trivially satisfied. Same shape, same result. ## The fix in the SQL itself **Qualify everything.** Give both tables aliases and prefix every column reference: ```sql DELETE FROM archive a WHERE a.order_id IN (SELECT s.order_id FROM staging s); ``` Now, if `staging` has no `order_id`, `s.order_id` is an error the engine raises before a single row is touched. The qualification converts a silent semantic disaster into a loud syntactic failure — which is the whole point. **Prefer explicit EXISTS.** Writing the correlation out makes the intended relationship visible: ```sql DELETE FROM archive a WHERE EXISTS (SELECT 1 FROM staging s WHERE s.order_id = a.order_id); ``` A reviewer can see at a glance which side of the comparison comes from where. In the `IN` form, the correlation is invisible precisely when it is wrong. ## Verifying blast radius before you commit Three habits, in increasing order of paranoia: 1. **Count first.** Swap the head of the statement and leave everything else identical: ```sql SELECT COUNT(*) FROM archive a WHERE a.order_id IN (SELECT s.order_id FROM staging s); ``` If that number is not in the range you expected, stop. A count equal to the table's total row count is the signature of this bug. 2. **Transaction, then inspect, then commit.** Open a transaction, run the delete, read the affected-row count the statement reports, and `ROLLBACK` if it is wrong. This is the only reliable undo SQL gives you, and it works only if you opened the transaction *before* the mistake — most drivers and consoles default to autocommit, so make it explicit. 3. **Bound the statement where the shape allows.** Deleting by an explicit key list, or by a predicate you have just counted, gives the statement a ceiling that a subquery bug cannot exceed. ## Why this survives review The statement reads correctly in English. "Delete from archive where order_id is in the staging order ids" is exactly what was intended, and the SQL appears to say it. Nothing in the text marks which table `order_id` came from, so a reviewer checking the logic finds nothing wrong — the defect lives in the schema's column names, not in the query's shape. That is why the rule is mechanical rather than a matter of judgment: **in any DML subquery, qualify every column reference.** It costs two characters per name and removes an entire failure class. ## Afterwards If it already committed, SQL has no answer — recovery is a backups and point-in-time-restore question. What you can do is make the next one impossible: qualified names as a review rule, destructive statements shipped as reviewed migrations rather than typed into a console against production, and counted previews recorded next to the statement they justify.
- Why did a handful of rows survive the runaway delete?Rows whose `order_id` is NULL. The predicate becomes `NULL IN (NULL)`, which evaluates to UNKNOWN rather than TRUE, and a DELETE removes only rows for which the search condition is TRUE. So the NULL-keyed rows are left behind — a confusing residue that often sends people looking for the wrong explanation.
- Would the same bug appear with EXISTS instead of IN?It can, but it is far harder to write by accident: `EXISTS` forces you to spell out the correlation predicate, so a missing column shows up as a comparison you can see and review. The `IN` form hides the correlation entirely — the mistake is invisible in the text, which is what makes it dangerous.
- What stops this from happening again beyond fixing the one statement?Make qualification a mechanical rule for every column in a DML subquery, so a wrong name fails loudly. Ship destructive statements as reviewed migrations rather than console one-liners, require a counted preview alongside the statement, and run them inside an explicit transaction so an unexpected row count can still be rolled back.
- Blames the engine rather than name resolution
- Assumes an unknown column in a subquery always errors
- Thinks IN and EXISTS are interchangeable regardless of correlation
- Skips the counted preview because the statement looks obvious
- Runs destructive DML under autocommit with no transaction
context
- Q-28357How do you write a DELETE whose target rows are chosen by a match in another table?level: middleimportance: should knowfreq 62%