1. Semi/anti joins: including "IN / NOT IN" tests operating on non-correlated sub-queries (
We can evaluate the sub-queries as sets and execute the outer query the same way as we now do IN clause.
2. Other non-correlated sub-queries: having a comparison operator with no modifier or a comparison operator modified by ANY, SOME or ALL (
a) The sub-query can/must be evaluated as a single value. (Like "= / !=" with no modifiers; Or in cases like "> / < / >= / <=" "ANY/SOME/ALL", either max() or min() can be applied to the sub-query).
b) In cases like "= / !=" "ANY / SOME / ALL", they are equivalent to category 2 queries.
3. General correlated sub-queries (
Need more sophisticated rewriting techniques to convert them into joins.
- if none of the dependence of inner query on the outer query is equi condition, we are currently unable to handle them.