Compare Join and SubQuery in performance?
Generally Join queries consume more memory than sub query. Sub query in turn involve intermediate table creation so affects performance, because of high I/Os fetches need for processing. If the RAM memory is more, then the Joins can be used instead of sub queries. Under memory constraints we can go for sub queries. Sometimes sub queries are flattened to join to improve the performance. Also making the outer query to compare inner query with equality option can materialize the subquery.
