why i'm not getting error? how does database understand relevant column in nested subquery?

Question

Here is the scenario: I have two tables department and employee. when i'm selecting a column from a table which doesn't exist in that table, it's throws error as expected. However, when i'm using subquery and again selecting the same column from the same table it's working. I don't understand how it can ignore my error.

create table department
( DEPT_ID                    NUMBER(2),
 DEPT_NAME                  VARCHAR2(6) );

 insert into department values(1,'ch');

 create table employee
 ( EMP_ID                     NUMBER(2),
 EMP_NAME                   VARCHAR2(6),
 EMP_DEPT_ID                NUMBER(2)
 );

 insert into employee values(0,'ch',1);

--getting  error for below (ORA-00904: "DEPT_ID": invalid identifier)
 select dept_id
from employee;

 -- not getting any error and can see the output for below sql statement. How it can consider invalid column for employee table dept_id in this query.
 select *
from   department
where dept_id in 
(
-- Incorrect column name
select dept_id
from employee
);

I have tried this with 2 RDBMS oracle and MSSQL. Case is the same with both. I didn't check with others

Answer 1

Since you don't qualify the columns, your query

select *
from   department
where dept_id in 
(
-- Incorrect column name
select dept_id
from employee
);

will be "evaluated" as

select d.*
from   department d
where d.dept_id in 
(
select d.dept_id
from employee e
);

A sub-query can reference its outer query's columns. Always qualify all columns when there are more than one table involved!

What you probably want is

select d.*
from   department d
where d.dept_id in 
(
select e.EMP_DEPT_ID
from employee e
);