>>>>Another sql question: How would one say "In table A, but not in table B" in sql?
>>>>thanks, Randy
>>>
>>>

>>>select tableA.* from TableA T1 LEFT JOIN  TableB T2 on T1.PK = T2.FK where T2.FK IS NULL

>>>
>>>this usually performs better than
>>>

>>>select * from TableA where PK not in (select FK from TableB)

>>>
>>>See interesting discussion at
>>>http://blog.sqlauthority.com/2008/04/22/sql-server-better-performance-left-join-or-not-in/
>>
>>Noami,
>>
>>You forgot the third possiblity
>>
>>SELECT * FROM TableA T1 WHERE NOT EXISTS(SELECT 1 FROM TableB WHERE FK = T1.PK)
>>
>>The EXISTS() clause is a very important and often underestimated insturment in resolving these types of queries. Above all it is a more natural translation of the asked question: Give me everything from table A which does not have records in table B.
>>
>>
>>
>>
>>Walter,
>
>And this is the 4th possibility
>
>http://msdn.microsoft.com/en-us/library/ms188055.aspx
>
>BTW, that's one of the questions I ask people on the iterviews.

Slightly different leage as the structure of the selected fields must match the both SQLs. I forgot about these ones as they are not available in VFP and can be easily simulated with EXISTS(). I believe (mean I did not check) from a performance pov, these commands are slower as the EXCEPT and INTERSECT can only be done when the two SQLs are evaluated (But maybe the optimizer is smarter than I suspect).