isn't it just looking for the student_id column, not finding it in the emp table, so its taking it from the student table?
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)
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass