Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cost of using subquery vs using same table twice in query

plsql noviceOct 15 2008 — edited Oct 15 2008
Hi all,

In a current project, I was asked by my supervisor what is the cost difference between the following two methods. First method is using a subquery to get the name field from table2. A subquery is needed because it requires the field sa_id from table1. The second method is using table2 again under a different alias to obtain table2.name. The two table2 are not self-joined. The outcome of these two queries are the same.

Using subquery:
select a.sa_id R1, b.other_field R2,
(select b.name from b
where b.b_id = a.sa_id) R3
from table1 a, table2 b
where ...
Using same table twice (table2 under 2 different aliases)
select a.sa_id R1, b.other_field R2, c.name R3
from table1 a, table2 b, table2 c
where
c.b_id = a.sa_id,
and ....
Can anyone tell me which version is better and why? (or under what circumstances, which version is better). And what are the costs involved? Many thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 12 2008
Added on Oct 15 2008
5 comments
2,410 views