>>>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#4bu-bu-but i like number 3 and number 1 must i use number 2?