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

Scope issue. A sub-query can reference its outer query's columns. Always qualify all columns when there are more than one table involved. — jarlh, Jun 29 '18 at 08:10
Please reference to the correct column names, even if SQL knows how to user outer references. This is why you have some errors. Its called emp_dept_id and not dept_id in employee — SqlKindaGuy, Jun 29 '18 at 08:19

jarlh · Accepted Answer · 2018-10-30T15:19:49.263

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
);

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

1 Answers1