It's not a secret anymore!*
The moose likes Oracle/OAS and the fly likes Subquery help needed Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Subquery help needed" Watch "Subquery help needed" New topic
Author

Subquery help needed

Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9345
    
    2

Guys,

I have the following relationship definition:

IDOne has a one to many relationship to IDTwo and IDTwo has a one to many relationship to IDThree. Now I have to fetch and present in a tabular format, all the IDOne and the associated IDTwo and IDThree. I was thinking to get this done as a subquery. Any alternative suggestions?


SCJP 1.4, SCWCD 1.4 - Hints for you, Certified Scrum Master
Did a rm -R / to find out that I lost my entire Linux installation!
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9345
    
    2

Would this hold good?

Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Why don't you simply join all three?
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9345
    
    2

Do you mean joins perform better than sub queries?
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
I prefer sub-queries if only I really needed. Depending on your data, table and index structure performance may change, but simple joins would do better most 8 of 10.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

One more thing. id3alias is not joined to any other tables.


[My Blog]
All roads lead to JavaRanch
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Joe Harry wrote:Do you mean joins perform better than sub queries?

It eventually depends on the actual database you're using, but generally join is the most common operation databases would be expected to do, therefore databases should be good at doing joins. Join would be my first choice, I go for subquery only when it is easier to write the SQL statement that way. With three tables involved, it is probably not the case.

Moreover, Oracle can internally process subqueries as joins (and vice versa). You can always compare the generated plans and performance of the two approaches, if still in doubt.
 
wood burning stoves
 
subject: Subquery help needed
 
Similar Threads
Many to one Relationship
joins
hibernate how to make a count and group by
Difference between MEMBER OF and IN operators
BDOM Multiplicities