Query with broken sub-select should result in error but returns rows

Question

I don't understand the behaviour in this case. In my understanding a query with an invalid sub-query should result in an error. But in this example it returns some rows.

Test-Data:

create table test_values ( tst_id number, tst_id2 number, tst_value varchar2( 10 ) );

create table test_lookup ( tst_id number, tst_value varchar2( 10 ) );

insert into test_values( tst_id, tst_id2, tst_value ) values ( 1, 2, 'a' );
insert into test_values( tst_id, tst_id2, tst_value ) values ( 1, 2, 'b' );
insert into test_values( tst_id, tst_id2, tst_value ) values ( 2, 2,'c' );
insert into test_values( tst_id, tst_id2, tst_value ) values ( 2, 2,'d' );

insert into test_lookup( tst_id, tst_value ) values ( 1, 'findMe' );

commit;

Works as expected:

select * from test_values where tst_id in ( select tst_id from test_lookup where tst_value = 'findMe' );

/*
    TST_ID    TST_ID2 TST_VALUE 
---------- ---------- ----------
         1          2 b         
         1          2 a   
*/

select tst_id2 from test_lookup where tst_value = 'findMe'; 
--ORA-00904: "TST_ID2": invalid identifier

But the following query is also retrieving lines, obviously by taking the "test_id2"-column from the "test_values"-table and not from the "test_lookup"-table as stated in the sub-query and though NOT using aliases for inner and outer parts.

select * from test_values where tst_id in ( select tst_id2 from test_lookup where tst_value = 'findMe' );

/*
   TST_ID    TST_ID2 TST_VALUE  
---------- ---------- ----------
         2          2 c         
         2          2 d         
*/

Answer 1

The reason is because when an unaliased column doesn't exist in the subquery but does exist in the outer query, Oracle assumes you are referring to the column from the outer query.

With aliases, the query you're confused about would look like:

select *
from   test_values tv
where  tv.tst_id in (select tv.tst_id2
                     from   test_lookup tl
                     where  tl.tst_value = 'findMe');

Hopefully, that makes things clearer?

The issue you're seeing is a very good example of why you should always label your columns with which table they came from - it makes it much easier to maintain the query for a start!