>>>>There're at least 3 ways you can do that
>>>>
>>>>* 1 - EXISTS subquery
>>>>SELECT * FROM mytable1 WHERE NOT EXISTS (SELECT 1 FROM mytable2 WHERE myfield1 = mytable1.myfield1)
>>>>* 2 - JOIN
>>>>SELECT mytable1.* FROM mytable1 JOIN mytable2 ON mytable2.myfield1 = mytable1.myfield1 ;
>>>> WHERE mytable2.myfield1 IS NULL
>>>>* 3 - IN subquery
>>>>SELECT * FROM mytable1 WHERE myfield1 NOT IN (SELECT myfield1 FROM mytable2)
>>>>
>>>>The second one most likely will be the fastest in VFP
>>>>
>>>
>>>
>>>Shouldn't #2 be a left join ?
>>
>>Yes, it should be a LEFT JOIN (was a typo, I assume). Check this link
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#4>
>
>bu-bu-but i like number 3 and number 1 must i use number 2?
If you have big data, can you test the performance of all 3 (once you switch from JOIN to LEFT JOIN)? See my discussion with Cetin - I'm a bit busy now to run tests.
If it's not broken, fix it until it is.
My Blog