I want to know if there is a difference between the two following queries.
If it is, is there a performance penalty?
Does this depend on the optimizer mode (cost, rule) ?
Does Oracle cashe the result from the subselect?
In my example there are 5,6 records on average type/groupid with about 10 groups.
The product table however, is large.
-- First Query
FROM product_type t, product p
WHERE t.groupid = 50
AND p.typeid = t.id;
-- Second query
FROM product p
WHERE typeid IN ( SELECT t.id FROM product_type t WHERE t.groupid = 50);