Greg Stark <gsstark@mit.edu> writes:
> I'm not sure but I think the way Oracle optimizes subselects is by
> transforming them into the equivalent join.
The point here is that there is no exactly equivalent join operation.
(Of course, given Oracle's known lack of standards-compliance on NULL
semantics, I wouldn't be overly surprised if they've misimplemented IN
in a way that doesn't preserve the spec's semantics ...)
It does get a lot simpler when the IN appears as a top-level WHERE
clause, because *in that context* you can ignore the difference between
FALSE and UNKNOWN results from IN. I have some other plans for
implementing IN in a join-like fashion in that special case. But what
I'm looking at right now is the general case ...
regards, tom lane