A DELETE with an IN-subquery emptied the whole table instead of a few rows — what went wrong?

## 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.