Subquery help needed

[Joe San]

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 San]

Would this hold good?

select id3alias.id from IDThree id3alias, IDTwo id2alias where id2alias.id = (select id2alias.id from IDOne id1alias where id1alias.id = &1)

[Fatih Keles]

Why don't you simply join all three?

[Joe San]

Do you mean joins perform better than sub queries?

[Fatih Keles]

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é]

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

[Martin Vashko]

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.