Which is better join or in?

Hi All.

I want to select data from one table BUT only if there is a reference to it in another table.

Which is better example A or Example B ?

Table Not_want pkey, data. more_data

Table want_from pkey, data_needed, use_data

example A

Select wf.data_needed, wf.use_data
from not_want nw, want_from wf
where nw.pkey = wf.pkey
;

example B

Select data_needed
from want_from
where wf.pkey in ( Select pkey from not_want )
;


thank you

Lot's of what-if's:

How much data is there total in each table?
What percentage of all rows returned will be in the result set?
What does the Explain Plan access paths reveal for each example?
What are your benchmark times for each (which performs better - wall clock)?
Are indexes worth the while (based on total rows returned in the result set) or SORTMERGE after full scans?

Benchmark!

Quote:

---

Originally posted by Grah

Select data_needed
from want_from
where wf.pkey in ( Select pkey from not_want )
;

---

Oracle almost always converts sub-queries into join before proceed

Instead of IN use EXISTS

select * from table1 where exists(select 'x' from table2 where
table1.col1=table2.col1);

regards
anandkl

This question has some reason with rule based optimazer.
in CBO, this is my mind, need get execution plan in any way.

I feel , using a dirct join comparison is better that using in clause ( reason could be based on the algoritham it follows for obvious reasons , it does lot of permutations in IN CLAUSE ) . u can check the same command on two different times by setting the clock on on your sqlplus . after clearing the sql parse )