William L. Thomson Jr. wrote:

>>
>> select <fields> from table1 t1
>> where not exists
>> (select 1 from table2 t2
>> where t1.field = t2.field)
>>
>>you've got a correlated subquery that will be optimized to walk (natural
>>scan) table1 and perform indexed lookups on table2 -
>
> Is that done on a per record bases "walk" as opposed to comparing entire
> data set? Seems more of a singleton select as opposed to a regular
> select statement?

Err, under the covers, all operations happen on one row at a time.
Firebird never materializes whole data sets, except as part of a sort.

> Using the 1? Not familiar with that syntax.

Just an arbitrary select list entry - cheap and small - that indicates
whether a record was found.

>
> The ones I am using are more like
>
> select <fields> from table1 t1
> where t1.field not in
> (select t2.field from table2 t2 where t1.field=t2.field)
>
> Not sure if the sub-selects where statement makes any difference or not.

Yes the where condition probably does make a difference in the
processing, though it's semantically irrelevant.

Regards,


Ann