Strange behavior with SQL server IN clause

Question

I am wondering why the below SQL statement behaves the way that it does:

select * 
from tableA 
where document_id in (select document_id
                      from  tableB 
                      where batch_id = 99997)

tableA contains the column document_id but tableB does not, so the query appears to return all rows in tableA, this behavior occurs if you use any field name in the select statement of the IN clause that is a field in tableA. Using a name not in tableA or tableB does result in an error.

Answer 1

It's not an error. In a subquery, you can still use columns form the parent. So when you say

SELECT document_id FROM tableB WHERE batch_id = 99997

You are saying for every row where batch_id is 9997 in tableB, select document_id from tableA. And of course, all those document_id values exists, so it will return all those rows.