Stephen,

Sorry for jumping into this discussion, it seems very interesting for me.

>>select * from table1;
>where keyfield NOT IN;
>(select keyfield from table2)
>
>>is not optimizable, and is extremely slow (about 15 minutes or more) for each query. I have tried a SCAN/SEEK, but it is at least as slow or worse, since you're looking for unique keys that don't exist, and therefore must hit every record.
>
>Try using a left join.
>
>select * from table1 left join table2 on table1.keyfield = table2.keyfield
> where isnull(table2.keyfield)
>
>This will return only the records from table1 where there is no match in table2.

I'm curious, it there a way to change NULL to be EMPTY on the fly?
I mean, SELECT Table1.*, table2.* from table1 left join table2 on table1.keyfield = table2.keyfield.
I need the whole result, but I don't need NULL in the final result. I want EMPTY values instead. Can I do this in SQL or I should use a special translation program (I already have one) for that?

TIA


>Cheers
>Steve Lea