Exactly what it is doing.

It is perfectly, completely valid for a subquery to reference a table in the outer query. The resolution is first to the tables within the subquery, if no table within the subquery has that column, the tables in the outer query are then checked. Since one of those columns matches the name, the query is completely valid.

You cannot have SQL throw errors if the column is not in the tables in the subquery, if that happened every single correlated subquery (eg EXISTS subqueries) would be invalid.

This is perfectly legal and must be perfectly legal

CREATE TABLE Table1 (Col1 INT);

CREATE TABLE Table2 (Col2 INT);

SELECT Col1 FROM Table1 WHERE EXISTS (select 1 FROM Table2 where col2 = col1) -- col1 in Table 1, Col2 in table 2

If you don't like unexpected surprises when you make a mistake with an IN, then follow recommended practices and always qualify your column names.

This would return the expected error

Select * from Student where Student.student_id in (Select emp.student_id from emp)