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

Question

I've got a query containing a subquery which always causes a SCAN of a very large table resulting in poor query times.

This is the query I'm using:

SELECT PersonId 
  FROM person 
 WHERE PersonId IN (
                    SELECT PersonId 
                      FROM relationship 
                     WHERE RelatedToPersonId = 12270351721
                   );

The query plan is reported as:

SCAN TABLE person (~100000 rows)
EXECUTE LIST SUBQUERY 1
SEARCH TABLE relationship USING INDEX relationship_RelatedToPersonId_IDX (RelatedToPersonId=?) (~10 rows)

The same query with a static list (equivalent to the results of the subquery):

SELECT PersonId 
  FROM person 
 WHERE PersonId IN (12270351727,12270351730,12270367969,12387741400);

And the query plan for that:

SEARCH TABLE person USING COVERING INDEX sqlite_autoindex_person_1 (PersonId=?) (~5 rows)
EXECUTE LIST SUBQUERY 1

Why would the first query prompt a scan if the second does not?

Answer 1

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

Answer 2

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.