Oracle : Query Runs Successfully, but the column name is not available

user_1864185 0
Viewed 51

In our application Oracle(11g and 12C), I'm facing a weird Issue as below.

When I run the below query in Oracle DB it runs successfully and gives me output. 

   select * from table1 where  col1  in (select col2 from table2 ) ;

But when I run the below inner query alone it throws the error:

select col2 from table2 ORA-00904: "COL2": invalid identifier

When I described the table table2, Col2 is not there. The error is expected. But the previous query executes successfully which is my concern.Could some one explain me this behavior?

oracle12c oracle11g oracle sql
1 Answers

Always, I repeat always give and use table aliases. You will never run into troubles and won't face such scenarios: Now run below query again in same DB, I'm sure it will throw some error:

select * from table1 a where  a.col1  in (select b.col2 from table2 b) ;

Now notice, all I did was give tables aliases. What most probably happening here is that table1 has a column named 'col2' and sub-query is referencing to that one. That's why it runs fine and doesn't show any error.

user_24502240
Related