At 16:00 17.10.2002 +1000, you wrote:

>Leyne, Sean wrote:
>
> > Next, why did you choose to use a FULL join? Wouldn't a LEFT JOIN been
> > more appropriate?
>
>I want the same records to appear, but with the SM_SELECTED.ID field
>either NULL
> when there is no record, or to have a value if the record has been
> selected.
>Wouldn't a left join exclude the records where the join fails?

A left (outer) join would only exclude records from the table on the right
side of the join, i.e. those (in theory) records in sm_selected that does
not exist in sawmembers. All records in sawmembers would be returned,
whether they have a matching record in sm_selected or not. A full join
would return records in sm_selected that have no matching records in
sawmembers, which is not what you want.

If status is in the sawmembers table, I guess all you want to do is to
replace full with left.

>Thanks for your thoughts, but still no explanation about why it should
>take 20
>times longer to do a query which has a lookup into a small table on it
>primary key.

A full join isn't quite the same as "looking up", it is more like mixing
two tables with records that may or may not correspond to eachother. I once
read that with a left join the only place to restrict records from the
right table was in the join, not in the where clause. I've never tested
whether this is true or not (never needed to), but if it is it could mean
that in a full join you have to put all criteria in your join, not in the
where clause. So, I wouldn't be surprised if your full join also returned
records that you really didn't want or found that it could use no indexes
at all. Basically, you've got two choices (I've removed id from your select
since it with a left join will be equal to auto_number or null if no match,
just add it back if you want it):

select sm.auto_number, ss.station_id from sawmembers sm
left join sm_selected ss on ss.id = sm.auto_number
where sm.status < 3
order by ss.station_id

or

select auto_number, (select station_id from sm_selected ss where ss.id =
sm.auto_number) as station_id
from sawmembers sm
where status < 3
order by 3

>I'm not familiar with IB_SQL. I haven't got much out of profilers in the

past.

It is pretty good and free from www.ibobjects.com. To write selects with
IB/FB, you really ought to be able to read plans (not necessarily fully
understand them) and preparing statements in IB_SQL is a good way to
discover things like this one. Your old solution will have a different plan
from your new one, I suspect it used an index for status, whereas the new
one giving you problems might not use any index at all. This would be easy
to see in IB_SQL and would have provided you with an ability to write your
question a bit different.

HTH,
Set