Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Strange SQL Speed
Message
From
19/10/2000 09:45:23
Cindy Winegarden
Duke University Medical Center
Durham, North Carolina, United States
 
 
To
19/10/2000 08:07:19
General information
Forum:
Visual FoxPro
Category:
Other
Miscellaneous
Thread ID:
00431066
Message ID:
00431420
Views:
13
>>>SELECT fieldx FROM table2 WHERE fieldx NOT IN (SELECT fieldx FROM table1)
>>
>>Try following query:
>>
>>SELECT fieldx FROM table2 WHERE NOT EXISTS (SELECT fieldx FROM table1 where table1.fieldx=table2.fieldx)
>>
>
>Your version requires the subquery to be run multiple times (once for each value of fieldx in table2). Dore's original version on needs to run the subquery once.
>
>In general, it's always a good idea to turn an EXISTS subquery into an IN subquery. You want to avoid referencing any table from the main query in the subquery - such a query is called "correlated" and is generally slower than a query that isn't correlated.
>
>Tamar

Turning the subquery into an IN query is fine but we are dealing here with something that is "not in."

There are special problems when testing for a value that is NOT IN because of the chance of there being a NULL value in the list of values to check for.

When there are nulls in the list, ANSI SQL-92 will always return the empty set. Rather than allowing the NOT IN syntax and risking this type of "surprise" result, FoxPro does not support the NOT IN syntax against a table which supports NULLs.

To test for values that are NOT IN the list one should use the syntax:
 ... WHERE NOT EXISTS ;
     (SELECT * FROM MyTable WHERE MyTable.Value = ValueImLookingFor)
Using the "slower" syntax means that the code won't "break" when someone decides to change the table to support NULLs in that field.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform