Thanks Naomi. I was wondering why such a big difference. I'll take a close look at JOIN.
>
>I got the same results even with index added. Inner JOIN is the fastest, but it produces different result:
>
>LOCAL i, lcList, lcQval, nSec, lcCmd
>* Create source data cursor
>CREATE CURSOR tmpSource (fs1 C(10), fs2 C(10))
>FOR i = 1 TO 1000000
> INSERT INTO tmpSource (fs1, fs2) ;
> VALUES (;
> 'F1' + PADR(TRANSFORM(m.i), 8, "0"), ;
> 'F2' + PADR(TRANSFORM(m.i), 8, "0"))
>ENDFOR
>INDEX ON fs1 TAG fs1
>*
>browse
>* Create and populate cursor and list of explicit query values
>lcStrList = ""
>CREATE CURSOR tmpFilter (t1 C(10))
>FOR i = 1 TO 10
> lcQval = 'F1' + PADR(TRANSFORM(m.i * 100), 8, "0")
> * Populate cursor for sub-select
> INSERT INTO tmpFilter (t1) VALUES (m.lcQval)
> * Build list of explicit values
> lcStrList = m.lcStrList + ["] + m.lcQval + [",]
>ENDFOR
>INDEX on t1 TAG t1
>lcStrList = RTRIM(m.lcStrList, 1, ",")
>*
>BROWSE
>
>* Now, the test
>* From explicit list of (comma separated) values
>nSec = SECONDS()
>lcCmd = "SELECT * FROM tmpSource WHERE fs1 IN (" + m.lcStrList + ") INTO CURSOR tmp nofilter"
>&lcCmd
>_cliptext = TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - From (list)"
>*
>* From sub-select
>nSec = SECONDS()
>SELECT * FROM tmpSource WHERE fs1 IN (SELECT t1 FROM tmpFilter) INTO CURSOR tmp nofilter
>_cliptext = _cliptext + CHR(13) + CHR(10) + TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - From Sub-select"
>*
>
>* From exists
>nSec = SECONDS()
>SELECT * FROM tmpSource WHERE exists (SELECT 1 FROM tmpFilter WHERE t1 = tmpSource.fs1) INTO CURSOR tmp nofilter
>_cliptext = _cliptext + CHR(13) + CHR(10) + TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - using Exist"
>*
>
>* Inner Join
>nSec = SECONDS()
>SELECT tmpSource.* FROM tmpSource INNER join tmpFilter ON tmpSource.fs1 = tmpFilter.t1 INTO CURSOR tmp nofilter
>_cliptext = _cliptext + CHR(13) + CHR(10) + TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - using INNER JOIN"
>
>
>USE IN tmp
>USE IN tmpSource
>USE IN tmpFilter>---------------------------------
>
>
>0.001 seconds to select 55 records - From (list)
>0.402 seconds to select 55 records - From Sub-select
>0.398 seconds to select 55 records - using Exist
>0 seconds to select 62 records - using INNER JOIN
