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

When we execute SELECT statement, the following steps are taken: 1) temporary table ( call it T ) is created 2) the number of rows T has equals to all possible combinations with which the rows from the two tables can be joined ( assuming there are more than one tables specified inside the FROM clause ) 3) rows that return FALSE ( when checked against conditions in the WHERE clause ) are deleted
4) Attributes that will not appear in the resulting table are deleted

When we execute the following SELECT statement ( outer query ), a temporary table T with 20 rows is created:

SELECT a1, a2

FROM       A
WHERE    (   SELECT       COUNT(*)
                    FROM          B
                    WHERE       B.b1 = A.a1


 )      >= 2

1. Is the inner SELECT ( subquery ) statement executed only once, or is it executed for each row ( thus it is executed 20 times ) of an outer temporary table T  T being created by outer SELECT statement ?
2. If inner statement is executed for each row of outer temporary table T, then I assume value A.a1 inside inner WHERE clause only equals to value of current row (of a temporary table T ) for which the inner statement is executed?
3. 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 ( so if T has 20 rows, inner statement will be executed 20 times, but each time it will produce the same result ) ?

thank you Received on Tue Jul 31 2007 - 13:07:59 CEST