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

On Jul 31, 7:07 am, beginner16 <kaja_love...@xxxxxxxxx> 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

.