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.?
sakthikumarAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
Just read this important link as it explain with examples on what kind of differences are there and how it works.

http://www.oracle-database-tips.com/oracle_subquery.html
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
It depends on how you actually write the query with correlated subquery and with joins.

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 ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
riazpkCommented:
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.
0
 
mpaladuguCommented:
   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
 
0
 
sakthikumarAuthor Commented:
sdstuber:

It looks like duplicate, But I need to know which one to use, if both options are available.
0
 
sdstuberConnect With a Mentor Commented:
in general you can guess before hand which one that will be based on the amount of reuse you'll get from the scalar query.
If the query  results will be used a lot, then you'll get the advantage of caching, if they won't, then you won't and a regular sub query might be better.

best option is to test both, use the one that is faster and scales better.

it's typically a trivial effort to try both ways
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.