Sairam Gaddam wrote: > sql="create table em(name text primary key,age text,pts text);"\ > "create table l(name text primary key,fame text);"; > > sql = "select * from em,l where l.fame=em.age"; > > 4 Once 0 13 0 00 > 5 OpenAutoindex 2 3 0 k(3,nil,nil,nil) 00 > 6 Rewind 1 13 0 00 > 7 Column 1 1 2 00 > 8 Column 1 0 3 00 > 9 Rowid 1 4 0 00 > 10 MakeRecord 2 3 1 00 > 11 IdxInsert 2 1 0 10 > 12 Next 1 7 0 03 > 13 Column 0 1 5 00 > 14 IsNull 5 24 0 00 > 15 SeekGE 2 24 5 1 00 > 16 IdxGT 2 24 5 1 00 > 17 Column 0 0 6 00 > 18 Copy 5 7 0 00 > 19 Column 0 2 8 00 > 20 Column 2 1 9 00 > 21 Column 2 0 10 00 > 22 ResultRow 6 5 0 00 > 23 Next 2 16 0 00 > 24 Next 0 4 0 01 > ... > > whenever the condition in the where clause is false, the program jumps to > the instruction pointed by p2 of SeekGe
Yes. > but if the condition proves to be false for the row 1 of both the > tables, then the program jumps to line 24(in this case) which > corresponds to outer table and takes the second row of outer table > for next iteration, then when will the program fetch 1st row > of table-1 and remaining rows of table-2 ??? In the join loop, this VDBE program does not fetch any rows from the second table: explain query plan select * from em,l where l.fame=em.age; 0|0|0|SCAN TABLE em 0|1|1|SEARCH TABLE l USING AUTOMATIC COVERING INDEX (fame=?) All accesses to "l" are actually handled by the temporary index (which is created by instructions 5..12). One index search is enough to determine whether a fame value exists. Regards, Clemens
