What version of SQL Developer are you using? Are either Persons or People public synonyms? In that case, a bug fixed for 3.0 could be the culprit: Bug 9460022 - WHEN USING PUBLIC SYNONYM THROWS ERROR ORA-00904: ... : INVALID IDENTIFIER
Otherwise you haven't given us much to go on. Have you tried that SQL in SQL*Plus logged in as the same user?
SQL Developer Team
I don't think this is a general SQL question.
select * from People where person_id in (
select person_id from Persons where name = 'Obama' -- sub-query
) and age > 18;
When I run the sub-query, I get:
ORA-00904: "PERSON_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
Error at Line: 5 Column: 8
This is because the table Persons don't have the field person_id.
But when I run the whole nested query it returns all rows in People with AGE greater than 18.
How come it succeeds when the sub-query obviously is erroneous?
Yes - that is a general SQL question and is ask pretty often.
Correlated sub-queries rely on the inner query being able to see and access columns from the outer query. Normally you see them referenced in the WHERE clause of the inner query and not in the SELECT clause but the reference is valid in either place. It works because the columns of tables in the main query are accessible to the subquery. So 'person_id' is likely a column of the 'people' table.
That can be a cause of 'odd' problems when the column (in your case 'person_id') is a column of more than one table.
Use an alias in the sub-query in the sub-query and you will find it will not succeed.
See these two AskTom articles where he discusses this exact issue