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

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 tvwhere  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!

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.