Hi Walter,

o.k, this is hard to test for a simple SELECT like this one. Maybe I place a UDF in the colum section of the SQL and count.

something like

PUBLIC lnCount
lnCount=0
SELECT * FROM tmpSource WHERE fs1 IN (SELECT t1+MyUDF() FROM tmpFilter) INTO CURSOR tmp
?lnCount

function MyDF
lnCount=lnCount+1
return ''
endfunc

As soon as you place something in the WHERE (especially if related to the outer select) of the inner SELECT this must fail.

But this was not the question.


Agnes
>Hi Agnes,
>
>
>>Sorin,
>>the subselect will be evaluated over and over. In your example for every record in the outer select. So there is a bit of work to do.
>
>AFAIK, that is not correct. The inner select is only executed once and placed in a temp file. Then for each record in the outer select it is searching the temp file for a match. The optimizer in the first approach just does do the opposite: Take each value in the IN clause and search that in the table directly using the index.
>
>AFAICS, the optimizer is not smart enough to see that both cases are equivalent. SQL server might be a bit smarter in this respect.
>
>Walter,