• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Subquery help needed

 
Joe Harry
Ranch Hand
Posts: 10045
3
Eclipse IDE Mac PPC Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Joe Harry
Ranch Hand
Posts: 10045
3
Eclipse IDE Mac PPC Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Would this hold good?

 
Fatih Keles
Ranch Hand
Posts: 182
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why don't you simply join all three?
 
Joe Harry
Ranch Hand
Posts: 10045
3
Eclipse IDE Mac PPC Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you mean joins perform better than sub queries?
 
Fatih Keles
Ranch Hand
Posts: 182
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One more thing. id3alias is not joined to any other tables.
 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic