Well that's pretty much what a hash join is doing - scan a table creating a hash table (effectively an in-memory index), then scan the inner table looking up va

Well that's pretty much what a hash join is doing - scan a table creating a hash table (effectively an in-memory index), then scan the inner table looking up values in the hash table. A hash table is a more effective data structure than a btree for in-memory search.