CLEAR 
IF .T.
CREATE TABLE testJoinPerf1(f1 I)
INDEX ON f1 TAG f1
CREATE TABLE testJoinPerf2(f2 I)
INDEX ON f2 TAG f2
FOR I=1 TO 10000
    INSERT INTO testJoinPerf1 VALUES (I)
NEXT 
FOR I=9901 TO 109900
    INSERT INTO testJoinPerf2 VALUES (I)
NEXT 
ENDIF
FOR I=1 TO 10
    nSeconds1=SECONDS()
    SELECT * FROM FORCE testJoinPerf1 JOIN testJoinPerf2 ON f1=f2 INTO CURSOR res
    nSeconds2=SECONDS()
    ?nSeconds2-nSeconds1,RECCOUNT("res") 
    
    USE IN res 
    
    nSeconds1=SECONDS()
    SELECT * FROM FORCE testJoinPerf2 JOIN testJoinPerf1 ON f1=f2 INTO CURSOR res
    nSeconds2=SECONDS()
    ?nSeconds2-nSeconds1,RECCOUNT("res") 
    USE IN res 
    ?
NEXT

SELECT  A.id, A.id_prod, A.id_detal, A.id_mtr, A.type_mtr, A.count, A.id_unit, A.id_form,  A.id_size, A.id_color, 
nvl(D.code, space(20))   as code,       nvl(D.naimen, space(100)) as naimen,  
nvl(M.naimen, space(100)) as material,  nvl(U.naimen, space(20))  as unit,   
nvl(F.naimen, space(20))  as form,   nvl(S.naimen, space(20))  as size,     
nvl(C.naimen, space(20))  as color       
FROM data1!prod_detal A         
LEFT JOIN data1!details    D  ON  D.id = A.id_detal         
LEFT JOIN data1!materials  M  ON  M.id = A.id_mtr         
LEFT JOIN data1!units      U  ON  U.id = A.id_unit         
LEFT JOIN data1!mtr_forms  F  ON  F.id = A.id_form         
LEFT JOIN data1!mtr_sizes  S  ON  S.id = A.id_size         
LEFT JOIN data1!mtr_colors C  ON  C.id = A.id_color         
WHERE A.id = ?lc_id AND A.id_prod = ?lc_id_prod

SELECT ... FROM xxx ;
WHERE Some IN ;
 (SELECT MAX(Another) FROM yyy ;
   WHERE yyy.ForLink = xxx.ForLink)

CREATE TABLE foo (f1 I, f2 I)
CREATE TABLE bar (f3 I, f4 I)
SELECT * from foo WHERE f1 IN (select f3 FROM bar WHERE f2=f4) INTO CURSOR res

SELECT * from foo WHERE f1 IN (select f3 FROM bar) INTO CURSOR res

SET COLLATE TO "MACHINE"
SET TALK OFF
CLEAR
CREATE TABLE tmp (nNum I, dDate D, nCode I)
FOR ln1 = 1 TO 1000
 FOR ln2 = 1 TO 60
  INSERT INTO tmp (nNum, dDate, nCode) VALUES ;
    (m.ln1, DATE() - m.ln2, IIF(m.ln2 > 30, m.ln2 - 30, m.ln2 + 30))
 ENDFOR
ENDFOR
INDEX ON nNum TAG nNum
INDEX ON dDate TAG dDate
CLOSE TABLES ALL
FOR ln1 = 1 TO 5
 lnSec = SECONDS()
 SELECT * FROM tmp t1 ;
  WHERE t1.dDate IN ;
   (SELECT MAX(t2.dDate) ;
     FROM tmp t2 ;
     WHERE t2.nNum = t1.nNum) ;
  ORDER BY t1.nNum INTO CURSOR xxx
 ? "Syntax 1. Try # ", m.ln1, "time ", SECONDS() - m.lnSec, "records ",
RECCOUNT("xxx")
 USE IN SELECT("xxx")
ENDFOR
CLOSE TABLES ALL
FOR ln1 = 1 TO 5
 lnSec = SECONDS()
 SELECT * FROM tmp t1 ;
  WHERE t1.dDate = ;
   (SELECT MAX(t2.dDate) ;
     FROM tmp t2 ;
     WHERE t2.nNum = t1.nNum) ;
  ORDER BY t1.nNum INTO CURSOR xxx
 ? "Syntax 2. Try # ", m.ln1, "time ", SECONDS() - m.lnSec, "records ",
RECCOUNT("xxx")
 USE IN SELECT("xxx")
ENDFOR
CLOSE TABLES ALL
FOR ln1 = 1 TO 5
lnSec = SECONDS()
 SELECT * FROM tmp t1 ;
  WHERE BINTOC(t1.nNum) + DTOS(t1.dDate) IN ;
   (SELECT BINTOC(t2.nNum) + DTOS(MAX(t2.dDate)) ;
     FROM tmp t2 GROUP BY t2.nNum) ;
  ORDER BY t1.nNum INTO CURSOR xxx
 ? "Syntax 3. Try # ", m.ln1, "time ", SECONDS() - m.lnSec, "records ",
RECCOUNT("xxx")
 USE IN SELECT("xxx")
ENDFOR
CLOSE TABLES ALL
FOR ln1 = 1 TO 5
 lnSec = SECONDS()
 SELECT * FROM tmp t1, ;
   (SELECT tmp.nNum, MAX(tmp.dDate) AS dDateMax FROM tmp GROUP BY tmp.nNum)
t2 ;
  WHERE t1.nNum = t2.nNum AND t1.dDate = t2.dDateMax ORDER BY t1.nNum INTO
CURSOR xxx
 ? "Syntax 4. Try # ", m.ln1, "time ", SECONDS() - m.lnSec, "records ",
RECCOUNT("xxx")
 USE IN SELECT("xxx")
ENDFOR

Syntax 1. Try #           1 time            3.563 records       10000
Syntax 1. Try #           2 time            3.516 records       10000
Syntax 1. Try #           3 time            3.547 records       10000
Syntax 1. Try #           4 time            3.500 records       10000
Syntax 1. Try #           5 time            3.516 records       10000
AVG=          3.5284
Syntax 2. Try #           1 time            2.688 records       10000
Syntax 2. Try #           2 time            2.641 records       10000
Syntax 2. Try #           3 time            2.656 records       10000
Syntax 2. Try #           4 time            2.609 records       10000
Syntax 2. Try #           5 time            2.625 records       10000
AVG=          2.6438
Syntax 3. Try #           1 time            2.297 records       10000
Syntax 3. Try #           2 time            2.250 records       10000
Syntax 3. Try #           3 time            2.344 records       10000
Syntax 3. Try #           4 time            2.235 records       10000
Syntax 3. Try #           5 time            2.250 records       10000
AVG=          2.2752
Syntax 4. Try #           1 time            2.281 records       10000
Syntax 4. Try #           2 time            2.234 records       10000
Syntax 4. Try #           3 time            2.234 records       10000
Syntax 4. Try #           4 time            2.250 records       10000
Syntax 4. Try #           5 time            2.235 records       10000
AVG=          2.2468

Syntax 1. Try #           1 time            2.281 records       10000
Syntax 1. Try #           2 time            2.234 records       10000
Syntax 1. Try #           3 time            2.219 records       10000
Syntax 1. Try #           4 time            2.219 records       10000
Syntax 1. Try #           5 time            2.235 records       10000
AVG=          2.2376
Syntax 2. Try #           1 time            2.860 records       10000
Syntax 2. Try #           2 time            2.813 records       10000
Syntax 2. Try #           3 time            2.812 records       10000
Syntax 2. Try #           4 time            2.812 records       10000
Syntax 2. Try #           5 time            2.828 records       10000
AVG=          2.8250
Syntax 3. Try #           1 time            2.265 records       10000
Syntax 3. Try #           2 time            2.235 records       10000
Syntax 3. Try #           3 time            2.235 records       10000
Syntax 3. Try #           4 time            2.219 records       10000
Syntax 3. Try #           5 time            2.219 records       10000
AVG=          2.2346
Syntax 4. Try #           1 time            2.125 records       10000
Syntax 4. Try #           2 time            2.063 records       10000
Syntax 4. Try #           3 time            2.109 records       10000
Syntax 4. Try #           4 time            2.062 records       10000
Syntax 4. Try #           5 time            2.093 records       10000
AVG=          2.0904