Distuinguish between compiled and executed. Sub-queries as expressions
are always compiled exactly once.
If the sub-query doesn't contain references to the outer query, it is
executed the first time it is needed and the results cached.
For example, if I have:
CREATE TABLE ab(a, b);
CREATE TABLE cd(c, d);
Then the subquery in the following is executed at most once and the
result cached:
SELECT a, (SELECT d FROM cd WHERE c = 'hello') FROM ab;
On the other hand, the subquery in the following is executed once for
each row in table 'ab' (because the value of 'b' may have changed):
SELECT a, (SELECT d FROM cd WHERE c = b) FROM ab;
Dan.
> Hi all;
>
> I know that nested queries in the FROM and WHERE clauses are compiled
> and run just once. Is that true for the nested queries in the SELECT
> clause as well?
>
> For example:
>
> SELECT firstname AS First,
> (SELECT name as Last
> FROM tbl_of_last_names
> WHERE name = 'Roger')
> FROM tbl_of_first_names,
> tbl_of_last_names,
> tbl_of_unique_people
> WHERE (tbl_of_first_names.PersonID = tbl_of_unique_people.ID)
> AND (tbl_of_last_names.PersonID = tbl_of_unique_people.ID)
> ;
>
> Is the SELECT name as Last compiled only once, or once for every row
> found in the outer query?
>
> --Keith
>
>
>
> I am running 2.8.15 on WinXP/2000.
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250
