Re: Dependent subquery stupidity: Is it me or the optimizer? ;-)

[KimSeong Loh]

If I am not wrong, in the 2nd query with the o1.i = 50, the inner subquery can be reduced into an independant subquery after o1.i is replaced with the constant 50 and only need to be executed once and obtain the value (result of the inner query) which is constant throughout all the rows in the outer query (that matches o1.i = 50).

In query 3, the value of o1.i could change for each rows in the outer query, so need to execute the inner query once for every row in the outer query.