Subject Re: [ib-support] Subselect vs. JOIN
Author Ann W. Harrison
At 11:18 AM 12/10/2002 +0000, Nico Callewaert <nico-callewaert@...> wrote:
>I was hoping to find a already posted answer in this group, but it
>seems not. The question is : I wonder when it's better to use a
>subselect or a join ? When I need only 1 field from a table I use
>a subselect, when I use several fields I use a join. Is that
>correct ? Because I think in a subselect you can only get 1
>field ? I wonder which one is the most performant ?

Generally, using FB1, a join is faster. However, to emulate
some subselects, you need to use an outer join - preserving the
primary table when the secondary table has no match. Outer
join optimization is .... suboptimal. If you do more than
one subselect on the same record, Firebird will go through all
the steps to find the record again, however, all the pages
referenced should be in cache, so the second lookup is very
cheap. Well, very cheap compared to the first one.


We have answers.