Sorry, meant to post this SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t2 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t3 (a INTEGER); sqlite> CREATE UNIQUE INDEX ndx on t3(a);
sqlite> EXPLAIN SELECT t1.a FROM t1 left join t2 using (a); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 7 0 00 Start at 7 1 OpenRead 0 2 0 0 00 root=2 iDb=0; t1 2 Rewind 0 6 0 00 3 Rowid 0 1 0 00 r[1]=rowid 4 ResultRow 1 1 0 00 output=r[1] 5 Next 0 3 0 01 6 Halt 0 0 0 00 7 Transaction 0 0 4 0 01 usesStmtJournal=0 8 Goto 0 1 0 00 sqlite> EXPLAIN SELECT t1.a FROM t1 left join t3 using (a); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 17 0 00 Start at 17 1 OpenRead 0 2 0 0 00 root=2 iDb=0; t1 2 OpenRead 2 5 0 k(2,,) 02 root=5 iDb=0; ndx 3 Rewind 0 16 0 00 4 Integer 0 1 0 00 r[1]=0; init LEFT JOIN no-match flag 5 Rowid 0 2 0 00 r[2]=rowid 6 SeekGE 2 12 2 1 00 key=r[2] 7 IdxGT 2 12 2 1 00 key=r[2] 8 Integer 1 1 0 00 r[1]=1; record LEFT JOIN hit 9 Rowid 0 3 0 00 r[3]=rowid 10 ResultRow 3 1 0 00 output=r[3] 11 Next 2 7 1 00 12 IfPos 1 15 0 00 if r[1]>0 then r[1]-=0, goto 15 13 NullRow 2 0 0 00 14 Goto 0 8 0 00 15 Next 0 4 0 01 16 Halt 0 0 0 00 17 Transaction 0 0 4 0 01 usesStmtJournal=0 18 Goto 0 1 0 00 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
