Re: Is inner query executed for each row of ... ?

On Jul 31, 7:07 am, beginner16 <kaja_love..._at_yahoo.com> wrote:
> hello
>
> When we execute SELECT statement, the following steps are taken:
> [snip]

Be aware that you are describing a conceptual implementation, not an actual one. It is highly unlikely that a real SQL implementation would materialize the cartesian-product intermediate unless there were no other choice.

> ...
> a) Is the inner SELECT ( subquery ) statement executed only once, or
> is it executed for each row

conceptually, it is executed for each outer row. References to outer-scope columns in the inner select are substituted with the values of each successive outer row.

> b) ... then I assume value A.a1 inside inner WHERE clause only
> equals to value of current [outer] row

Yes

>
> c) But if the inner SELECT statement doesn't have a WHERE clause, or
> if there are no attributes from table T specified inside inner WHERE
> clause, then I assume that inner SELECT statement returns same result
> for all rows

Yes.

Remember that the SQL engine is allowed to choose a different execution plan that does NOT involve executing the inner select over and over, as long as it gets the same answer as the conceptual model you describe above.

Karl Received on Tue Jul 31 2007 - 15:15:20 CEST