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!

Answer 2

When your 'broken' query is used as a subquery, it can still refer to the outer query's table columns; that's necessary for correlation to work. It's picking up the tst_id2 column from the test_values table. If both tables had the same column then the inner table would take precedence, but that isn't the case here.

From the documentation:

Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.

You can see what's happening by adding table aliases; this still errors:

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

ORA-00904: "TL"."TST_ID2": invalid identifier

But this works:

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

It is explicitly using test_values column (via the tv alias); your original query was doing the same, but implicitly.