Why does a subquery cause a scan when a static list does not?

Probably unmatched datatypes. Does person.PersonId and relationship.PersonId have the same data definition.

The table has to be scanned because you're including another field (RelatedToPersonId) in the WHERE clause of your subquery. The list of PersonIDs can go directly to the index.

I cannot reproduce. I executed the following statements on a fresh SQLite version 3.7.8 install:

create table person (id int not null primary key, name varchar(20));insert into person values (1, 'a');insert into person select id + 1, name || 'b' from person;insert into person select id + 2, name || 'c' from person;insert into person select id + 4, name || 'd' from person;insert into person select id + 8, name || 'e' from person;insert into person select id + 16, name || 'f' from person;insert into person select id + 32, name || 'g' from person;insert into person select id + 64, name || 'h' from person;insert into person select id + 128, name || 'i' from person;insert into person select id + 256, name || 'j' from person;insert into person select id + 512, name || 'k' from person;insert into person select id + 512, name || 'l' from person;insert into person select id + 1024, name || 'l' from person;insert into person select id + 2048, name || 'm' from person;insert into person select id + 4096, name || 'n' from person;insert into person select id + 8192, name || 'o' from person;insert into person select id + 16384, name || 'p' from person;insert into person select id + 32768, name || 'q' from person;insert into person select id + 65536, name || 'r' from person;select count(*) from person;create table relation (id int, related int);insert into relation select id, id + 1 from person;insert into relation select id, id + 2 from person;insert into relation select id, id + 3 from person;insert into relation select id, id + 4 from person;insert into relation select id, id + 5 from person;insert into relation select id, id + 6 from person;insert into relation select id, id + 7 from person;insert into relation select id, id + 8 from person;insert into relation select id, id + 9 from person;insert into relation select id, id + 10 from person;delete from relation where related not in (select id from person);create index relatedToPerson on relation(related);explain query plan select id from person     where id in (select id from relation where related = 2345);

The results for the query plan statement:

0|0|0|SEARCH TABLE person USING COVERING INDEX sqlite_autoindex_person_1 (id=?)(~25 rows)0|0|0|EXECUTE LIST SUBQUERY 11|0|0|SEARCH TABLE relation USING INDEX relatedToPerson (related=?) (~10 rows)

Why doesn't it work for you? The reasons I can think of:

• your table relationship doesn't contain the column PersonId (pleaseverify)
• you are using another version of SQLite
• you have other constraints, for example unique indexes.

Can you run the script above and verify if you get the same results I got?