>>>>

SELECT state FROM table2 ;
>>>>  WHERE State NOT IN (SELECT state FROM table1)
>>>>* or using correlated query
>>>>SELECT state FROM table2 ;
>>>>  WHERE NOT EXISTS (SELECT state FROM table1 WHERE state = table2.state )
>>>>
>>>>

>>>
>>>While VFP might optimize a correlated subquery like yours, in generally, when you can write a readable query without a correlated subquery, it's a better choice. (To the best of my knowledge, any correlated EXISTS query can be rewritten as an IN query, but sometimes the result is pretty obscure.)
>>>
>>
>>I've to disagree with you here because wich query is faster depends on the query itself and data it is ran against. I don't know about any tests that shows that query without a correlated subquery is always faster. Also if you move to Sql Server you will find that correlated subquery is recomended over other approch because it's faster in most cases.
>
>The point is that in most cases, for a correlated subquery, the subquery has to be executed once for each value referenced from the outer query. (In your example, that's table2.state.) I suspect that VFP recognizes and optimizes this case, so that there's no penalty, but I also suspect that in more complex cases, you may pay it.

Exactly. That's why in some cases it could be faster. I would like to note that it's applicable to the EXISTS type of subqueries only because they don't retrive the data but just check for existence of records.


>
>I can't speak for SQL Server because I don't have experience with it.
>
>Tamar