Hi!

Ok, I have a question about correlated query. Why you think it will be much slower? In my sample only one record will be selected in the subquery. When indexes used, this will be quick. Than, for all records of table 1 we select 1 record in subquery in table 2. N2+N1 items selected. In the original sample I see complexity is N2*SQRT(N1), because we have to search result of subquery for 'IN' condition for each record in table 2.

SO, PLeeeeese, explain why my query will run slower when optimization used???

>>>>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.