Yes, the "null" method blew my mind when I first encountered it (in an example much more complex, using a join Select). Using it has trimmed 80% off the SQL times (against SQL Server 2014) in our large queries, compared to IN SELECT. SQL Server, for one, produces the same Query Plan for EXISTS and IN SELECT, in case anyone is wondering.

It's like adding a new complex dance step to one's repertoire (e.g., in Zumba Gold, aka Zumba for Old Farts like me ;) ). Very clumsy until it's incorporated, and then it's just "it".


>Probably correct (although there is a chance of reusing existing index even in vfp for subquery result...) but as the question was
>titled "SQL question" citing the ones more expressive over a version less intuitive but faster is
>"Programming to the interface" and not "to the implementation" ;-)
>
>Chances are that fastest solution in vfp might not use SQL, but old xBase ;-)
>
>And even in SQL join a lot depends on implementation: imagine one where the actual steps done are filtering out the null-rows not in "where" step halting writing but more like a "having" filter after hitting the disk writing out the join.
>
>Would be perfectly "legal" from SQL POV...
>
>
>>on your size ables the queries supplied by Thomas and Gregory will run about equally fast. If you have large tables, Gregory's query will run much faster.
>>
>>Hank
>>
>>>Hi:
>>>I have two tables: table1 and table2. Each has one text field in common: ID, containing a 9 character code that is unique for each record. However, whereas table1 has 10,000 records, table2 has 200. But some of the ID's found in table2 do not exist in table 1. Using set relation and filter, I can find the table1 id's that are missing from table2. I am wondering whether this could also be done with an SQL statement?
>>>
>>>

>>>CLOSE ALL
>>>USE
>>>USE table2
>>>INDEX on id TO id2
>>>
>>>SELECT 2
>>>USE table1
>>>INDEX on id TO id1
>>>
>>>SELECT 1
>>>SET RELATION TO id INTO table2
>>>SET FILTER TO table2.id  == " "  && or == ""
>>>SELECT 2
>>>DELETE ALL 
>>>SET FILTER TO 
>>>SET FILTER TO DELETED()
>>>BROWSE && for instance
>>>

>>>
>>>I thought that an SQL statement such as the following might be equivalent, but it isn't:
>>>
>>>

>>>SELECT * ;
>>>FROM table1, table2 ;
>>>WHERE table1.id <> table3.id 
>>>

>>>
>>>Thank you, Steve