[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

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.?
0
sakthikumar
Asked:
sakthikumar
2 Solutions
 
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
 
Naveen KumarProduction 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
sdstuberCommented:
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now