title: Why is SQLite refusing to use available indexes when adding a JOIN?
body: <p>This is related to <a href="https://stackoverflow.com/questions/19454787/why-is-sqlite-refusing-to-use-available-indexes">Why is SQLite refusing to use available indexes?</a></p>
  
  <p>The queries to create the database is:</p>
  
  <pre>CREATE TABLE foo(id TEXT);
  CREATE INDEX `foo.index` ON foo(id);
  CREATE TABLE bar(id TEXT);
  CREATE INDEX `bar.index` ON bar(id);
  CREATE VIEW baz AS SELECT id FROM foo UNION ALL SELECT id FROM bar;
  CREATE TABLE bam(id TEXT, value TEXT);
  
  INSERT INTO foo VALUES('123');
  INSERT INTO foo VALUES('1123');
  INSERT INTO foo VALUES('2123');
  INSERT INTO foo VALUES('3123');
  
  INSERT INTO bar VALUES('44123');
  INSERT INTO bar VALUES('441123');
  INSERT INTO bar VALUES('442123');
  INSERT INTO bar VALUES('443123');
  </pre>
  
  <p>The result of <code>EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON baz.id=bam.id WHERE baz.id IN ('123', '234');</code> is:</p>
  
  <pre>SCAN TABLE foo (~1000000 rows)
  SCAN TABLE bar (~1000000 rows)
  COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
  SCAN SUBQUERY 1 (~2000000 rows)
  EXECUTE LIST SUBQUERY 4
  SEARCH TABLE bam USING AUTOMATIC COVERING INDEX (id=?) (~7 rows)
  </pre>
  
  <p>EDIT: Interestingly enough if I do <code>EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM baz WHERE baz.id IN ('123', '234')) AS t LEFT JOIN bam ON t.id=bam.id ;</code> it still doesnt use the index but if i do <code>EXPLAIN QUERY PLAN SELECT * FROM baz WHERE baz.id IN ('123', '234');</code> it does. What is going on?</p>
  
  <p>Why isnt it using the indexes on foo and bar?? It does use the indexes without the JOIN section as apparent in the linked question.</p>
  
  <p>SQL Fiddle: <a href="http://sqlfiddle.com/#!7/32af2/14" rel="nofollow noreferrer">http://sqlfiddle.com/#!7/32af2/14</a> (use WebSQL)</p>