List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:May 6 2001 8:34pm
Subject:RE: derived tables?
View as plain text  
Hi!

>>>>> "Braxton" == Braxton Robbason <robbason@stripped>
> writes:

Braxton> sorry, I guess I wasn't clear.
Braxton> By derived tables I mean the following (i.e. in the from clause):

Braxton> select a.col1, b.col2
Braxton> from (select max(col1) as col1 from root_table ) a,
Braxton>   other_table b
Braxton> where a.col1=b.col1;

Braxton> whereas a subquery (in a sub-select query) is in the where clause:

Braxton> select a.col1, b.col2
Braxton> from root_table a,
Braxton>   other_table b
Braxton> where a.col1=b.col1
Braxton>   and a.col1 in (select col1 from third_table);

ok.

Braxton> I think it's actually harder to implement subqueries than derived tables,
Braxton> because the derived table example above will give the same results as the
Braxton> following two-pass sql:

Braxton> create temporary table temp_table as select max(col1) as col1 from
Braxton> root_table;

Braxton> select a.col1, b.col2
Braxton> from temp_table a,
Braxton>   other_table b
Braxton> where a.col1=b.col1;

Braxton> There is no similar way of converting a sub-select query into sql that is
Braxton> currently understood by mysql.

I agree.

Braxton> Does this clarify what I am talking about somewhat? There are no problems
Braxton> that can only be solved with derived tables, but having them would make
Braxton> aspects of my work easier.

Yes, derived tables would be nice and I shall add these to our todo.

Regards,
Monty
Thread
derived tables?Braxton Robbason5 May
  • derived tables?Michael Widenius5 May
    • RE: derived tables?Braxton Robbason5 May
      • RE: derived tables?Michael Widenius6 May