Please consider the following query:
select * from parent p where p.id in ( select p.id from parent p inner join child c on c.parentId = p.id where p.age > 50 )
Is it safe (in general!) to rewrite the query to this, in order to save a join? The subquery contains the same alias as the outer query (not a typo).
select * from parent p where p.id in ( select p.id from child c where c.parentId = p.id and p.age > 50 )
My concerns are that the subquery's where-statement is going to be applied to the outer query's result. Is that reasoning correct?
Please note that the query above is just a simple example and the real query is big, where the sub-select is somewhere in some or-condition and contains several joins.