Performance for sub-select or joins

Hi

we just had an arguement about to use sub-selects instade of joins and i'm wondering what your opinions are, regarding this matter. The tables are about 100 rows large and the OS is UNIX. In my opinion the join is faster and according the explain plan i'm still better of with the join, but is there any reason why i should use a sub-select ?

I know that there could be a problem when the sub-select returns more than one row.

any suguestions ??

tia
eugen

view1 with join

create or replace view xyz (field1, field2, ....) as select a.field1, b.field2, ...
from table a, table b, table c

where a.primary_key = b.foreign_key1
and   b.foreign_key2 = c.field
and   c.primary_key = variable;

against

create or replace view xyz (field1, field2, ....) as select a.field1, b.field2, ...
from table a, table b
where a.primary_key = b.foreign_key1
and b.foreign_key2 = (select c.field

                        from table c
                        where   c.primary_key = variable);

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 06 2000 - 00:00:00 CDT