Re: ORDER BY <field not in return list>

On Mon, 25 Jul 2005, Jeffrey W. Baker wrote:

> On Mon, 2005-07-25 at 19:08 -0300, Marc G. Fournier wrote:
>>
>> On Mon, 25 Jul 2005, Jeffrey W. Baker wrote:
>>
>>> On Mon, 2005-07-25 at 18:11 -0300, Marc G. Fournier wrote:
>>>> Just curious as to whether or not a warning or something should be issued
>>>> in a case like:
>>>>
>>>> SELECT c.*
>>>> FROM company c, company_summary cs
>>>> WHERE c.id = cs.id
>>>> AND cs.detail = 'test'
>>>> ORDER BY cs.fullname;
>>>
>>> Seems like it should work. Is it not returning in fullname order in
>>> your tests?
>>
>> Full name isn't a field in the results, so how would it be ORDERing based
>> on it? fullname is a field in the table being joined in order to restrict
>> the results to just those with cs.detail = 'test' ... but company itself
>> doesn't have a field fullname ...
>
> I'm still not seeing the problem. cs.fullname is in the product of the
> join, and you can order the result thereby, and not return the column.

'k, this creates another question ... "product of the join" ... if I do a
join on three tables, let's say that each has 10 fields in them (highly
unlikely, but humor me) ... do the joins end up creating, in memory, a
"temporary table" that includes all 30 fields?

Basically, from how everyone has explained it, the ORDER BY will be done
after all the JOINs are done, and the "product of the joins" are complete
... for it to be performed on a field not in the SELECT <field> clause,
then those fields have to be "loaded into memory", *then* ORDERed, and
then the query would return teh result set out of those "mega table" ...

Is this correct?

From what I've read, I believe this to be so ... but figured I'd double
check to make sure I'm not missing something ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664