>2) When you select with an order by, you should use the subselect.

I'd expect it to not matter all too much, but unless you wanted to order on
the subselect (in which case I'd definetely recommend a join), I guess
you're right in that it could be faster.

The most important will be to have correct indices. You've already got your
primary keys, in addition you would most likely need an index for
SampleDetail.MasterID (and possibly more indices depending on your
WHERE-clauses).

I generally tend to use JOINs more often than subqueries, but that might be
because I rarely do need 0 or 1 rows returned from a table (which you would
require for a subselect).

Set

At 14:04 14.02.2001 +0100, you wrote:

>Hi Jörg
>
>I have asked the same question half a year ago in the IBO-Mailinglist. These
>are the results in short words:
>1) When you just do a select without an order by, it doesn't matter, if you
>use a Join or a SubSelect.
>2) When you select with an order by, you should use the subselect.
>
>
>Guido.
>
>Jörg Schiemann wrote:
>
>> Hi,
>>
>> I've a master and a detail table like
>>
>> create table "SampleMaster" (
>> MasterID integer not null primary key,
>> OrderNo integer not null,
>> Comment varchar(20),
>> .
>> .
>> .
>> );
>>
>> and
>>
>> create table "SampleDetail" (
>> DetailID integer not null primary key,
>> MasterID integer not null,
>> OrderPos integer not null,
>> quantity integer not null,
>> .
>> .
>> .
>> constraint fk_MasterID foreign key (MasterID) references SampleMaster
>> (MasterID)
>> );
>>
>> Is a query with a subquery faster then a joined query if I want all fields
>> from the SampleDetail and only OrderNo from SampleMaster?
>>
>> TIA
>>
>> Jörg Schiemann