>>>SELECT fieldx FROM table2 WHERE fieldx NOT IN (SELECT fieldx FROM table1)
>>
>>Try following query:
>>
>>SELECT fieldx FROM table2 WHERE NOT EXISTS (SELECT fieldx FROM table1 where table1.fieldx=table2.fieldx)
>>
>
>Your version requires the subquery to be run multiple times (once for each value of fieldx in table2). Dore's original version on needs to run the subquery once.
>
>In general, it's always a good idea to turn an EXISTS subquery into an IN subquery. You want to avoid referencing any table from the main query in the subquery - such a query is called "correlated" and is generally slower than a query that isn't correlated.
>
>Tamar

Turning the subquery into an IN query is fine but we are dealing here with something that is "not in."

There are special problems when testing for a value that is NOT IN because of the chance of there being a NULL value in the list of values to check for.

When there are nulls in the list, ANSI SQL-92 will always return the empty set. Rather than allowing the NOT IN syntax and risking this type of "surprise" result, FoxPro does not support the NOT IN syntax against a table which supports NULLs.

To test for values that are NOT IN the list one should use the syntax:

 ... WHERE NOT EXISTS ;
     (SELECT * FROM MyTable WHERE MyTable.Value = ValueImLookingFor)

Using the "slower" syntax means that the code won't "break" when someone decides to change the table to support NULLs in that field.