Is inner query executed for each row of ... ?
Date: Tue, 31 Jul 2007 04:07:59 -0700
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
- 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 ?
- 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?
- 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