I'm frankly surprised that there are many in(subselect) queries in the core, as it was common knowledge around aD when I was there (straight through the release of 4.x) that in = bad, exists = good. I can't provide a behind-the-scenes look at Oracle's behavior in these cases, but I've seen it in action on complex queries on Oracle (hacking on photo.net). Not only are "in" queries immensely slow relative to "exists" queries, but they will error if the result set of the subselect is too large because it has to store the returned rows instead of streaming them through the rest of the query in parallel.
