sakthikumar
asked on
correlated subquery vs simple joins
If for a query, we can use both correlated subquery and joins,
which ones are better, when both produce the same result.?
which ones are better, when both produce the same result.?
just read through these for more information...
http://www.dba-oracle.com/oracle_tips_subq_rewrite.htm
http://stackoverflow.com/questions/611676/oracle-correlated-subquery-in-from-list
http://www.dba-oracle.com/oracle_tips_subq_rewrite.htm
http://stackoverflow.com/questions/611676/oracle-correlated-subquery-in-from-list
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Like everything, it depends. Test, Test, Test. This is what i can say.
Also bear in mind that
(1) sub-query might return different result set than join and you might have to use DISTINCT in order to get equivalent results for both cases.
(2) sub-query MIGHT NOT be equivalent of an equi-join so you might be forced to use outer join which in turn, might force optimizer to eliminate some of access paths.
Sub-query might be useful when you want to get first row as soon as possible. And sometimes, i have used sub-query (along with ROWNUM>0) to avoid merging with the main query and being executed separately.
In conclusion, nothing is superior over other always. If it would have been the case, Oracle would have only the superior one.
Also bear in mind that
(1) sub-query might return different result set than join and you might have to use DISTINCT in order to get equivalent results for both cases.
(2) sub-query MIGHT NOT be equivalent of an equi-join so you might be forced to use outer join which in turn, might force optimizer to eliminate some of access paths.
Sub-query might be useful when you want to get first row as soon as possible. And sometimes, i have used sub-query (along with ROWNUM>0) to avoid merging with the main query and being executed separately.
In conclusion, nothing is superior over other always. If it would have been the case, Oracle would have only the superior one.
The advantage of using joins is, yon can select any column from both the tables
in you select statement.
where as in a correlated sub-query you will not be able select columns from a table used inside a
sub-query
in you select statement.
where as in a correlated sub-query you will not be able select columns from a table used inside a
sub-query
ASKER
sdstuber:
It looks like duplicate, But I need to know which one to use, if both options are available.
It looks like duplicate, But I need to know which one to use, if both options are available.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think Joins are the best if you have proper indexes and good data designs which link tables properly. At time, the correlated subqueries are internally changed to make use of join type queries by SQL engine.
Can you post both versions of the queries please ?