Why does SELECT * FROM customers c WHERE c.id IN (SELECT id FROM orders_archive) return every customer when orders_archive has no id column?
answer
- the inner table has no such column
- name lookup does not stop at the subquery
- unqualified names are searched outward
- it silently became a correlated reference
- qualify inner columns and it errors instead
basics
~20 sThe unqualified id resolves outward to customers.id, so the subquery returns the current customer's own id for every archive row and the IN predicate is trivially true. Qualifying every inner column with its table alias turns the mistake into an error.
solid answer
~50 sSQL resolves an unqualified column name in the innermost scope where it exists, and if it is not found there it keeps walking **outward** into the enclosing query blocks. `orders_archive` has no `id`, so `SELECT id` binds to `c.id` from the outer query — the subquery becomes accidentally correlated and yields the current customer's own id, repeated once per archive row. `c.id IN (that same id, …)` is true for every customer, so the query returns the whole table. The same mistake inside a `DELETE` is how people wipe a table. There is no syntax error because the query is perfectly legal — it just does not mean what the author intended. The fix is a habit, not a flag: alias every table and qualify every column inside a subquery, so a wrong name fails loudly instead of silently correlating.
code
sql · 5 lines-- customers(id, name); orders_archive(archive_id, cust_id) -- note: no "id" column
SELECT * FROM customers c
WHERE c.id IN (SELECT id FROM orders_archive);
-- "id" resolves outward to c.id, so the predicate reads c.id IN (c.id, c.id, ...)
-- every customer matches; with an empty orders_archive, none dogo deeper
Recall that an unqualified column inside a subquery can refer to the outer table, and that this is why you alias tables and qualify columns. Being able to name the danger is enough at this level.
Explain the innermost-first, then outward name-resolution rule and walk the predicate to show why every row matches. Interviewers expect you to produce the corrected, fully qualified statement on the spot.
Show the operational side: this shape is a data-loss incident in a DELETE, so talk about proving the predicate with a SELECT first, and about review habits that make the correlation explicit rather than inferred.
Own the prevention: a lint or review standard requiring aliased tables and qualified columns, plus guarded write paths, converts a whole class of silent semantic bugs into parse-time failures for everyone in the codebase.
## The resolution rule that causes it Every query block introduces a scope containing the range variables (table aliases) of its own `FROM` clause. When SQL meets an unqualified column name it looks in the innermost scope first; if no table there has that column, it searches the next enclosing query block, and so on outward. Only if the name is found nowhere is it an error. That outward walk is exactly the mechanism that makes deliberate correlation possible — and it is also what makes accidental correlation invisible. So with `customers(id, name)` and `orders_archive(archive_id, cust_id)`: ```sql SELECT * FROM customers c WHERE c.id IN (SELECT id FROM orders_archive); ``` The inner `id` is not a column of `orders_archive`. Instead of failing, it binds to `c.id` from the enclosing block. The subquery is now correlated, and for the customer currently being tested it returns that customer's own id — once for every row in `orders_archive`. ## Why that matches everything The predicate becomes `c.id IN (c.id, c.id, c.id, …)`, which is true whenever the list is non-empty. Every customer passes, so a `SELECT` returns the full table and a `DELETE` with the same `WHERE` removes every row. This is one of the classic ways production data disappears, and it is why the shape is asked about in interviews. The edge case is instructive: if `orders_archive` happens to be **empty**, the subquery returns no rows, `IN` over an empty set is false for every row, and the query matches **nothing**. The same broken statement therefore behaves in two opposite ways depending on data — matching all rows or none — and never raises an error in either case. ## Why the engine cannot help you There is nothing to complain about. The statement is syntactically valid and semantically well defined; the resolution rule did exactly what the standard says. No engine can know that you *meant* `orders_archive.cust_id`. That is the whole lesson: correctness here is a property of how you write names, not something the parser can enforce for you. A related trap is a name that exists in **both** tables. Then the unqualified reference silently binds to the inner one — the opposite direction, but the same class of surprise. Someone later renames or drops that inner column and the query keeps running while quietly changing meaning, because the name now resolves outward instead. ## The fix: qualify everything ```sql -- fails loudly: there is no a.id SELECT * FROM customers c WHERE c.id IN (SELECT a.id FROM orders_archive a); -- what the author meant SELECT * FROM customers c WHERE c.id IN (SELECT a.cust_id FROM orders_archive a); ``` Give every table in every block an alias, and qualify every column reference with it. A typo or a stale column name then produces `column a.id does not exist` at parse time instead of a silently correlated predicate. This is the single highest-value formatting rule in subquery-heavy SQL, and it costs nothing. Two more habits reduce the blast radius: - **Run the inner query on its own first.** An uncorrelated subquery is a complete statement; if it fails standalone with "unknown column", you have just found an accidental correlation. This is also the reason `SELECT` the rows before you `DELETE` them: the two statements share the same predicate, so proving the predicate on a `SELECT` proves it for the delete. - **Prefer `EXISTS` with an explicit correlation.** `WHERE EXISTS (SELECT 1 FROM orders_archive a WHERE a.cust_id = c.id)` states the join condition where a reader can see it, so an unintended one has nowhere to hide. ## Reading a query for accidental correlation When reviewing, cover the outer query and ask whether the inner block would still run standalone. If it would not, the correlation must be one you meant. Then check every unqualified name in the inner block against the inner `FROM` list specifically — not against your mental model of the schema. Those two passes catch essentially all of this class. ## Common mistakes - Assuming an unknown column in a subquery is always an error. - Believing the subquery is validated in isolation before the outer query runs. - Blaming `IN` for the behaviour, when `= ANY`, `EXISTS` or a join written the same way would all be equally wrong. - Treating column qualification as a style preference rather than a correctness guard.
- What does the same broken statement return if orders_archive is empty?No rows at all. The subquery yields an empty set, and `IN` over an empty set is false for every outer row. So the identical statement matches everything when the archive has rows and nothing when it is empty — with no error either way, which is what makes the bug so hard to spot in testing.
- What happens if the column name exists in both the inner and the outer table?The inner one wins: resolution starts in the innermost scope and stops as soon as it finds a match. The query works, but a later schema change that drops or renames that inner column silently flips the reference outward instead of failing — another reason to qualify every name.
- How would rewriting the predicate with EXISTS have prevented the bug?`WHERE EXISTS (SELECT 1 FROM orders_archive a WHERE a.cust_id = c.id)` puts the correlation in plain sight as an explicit equality between two qualified columns. An accidental outer reference has nowhere to hide, because every column in the predicate is written with its owning alias.
It is the SQL version of an inner code block silently using a variable from the enclosing scope instead of failing to compile: the name resolves, so nothing complains, but it refers to the wrong thing.
saying these in an interview costs you the question
- Assumes an unknown column inside a subquery always raises an error
- Thinks the subquery is validated independently of the outer query
- Blames IN rather than the unqualified column reference
- Says qualifying columns is only about readability
- Cannot explain why the same statement matches nothing on an empty inner table