Re: How to handle results with column names clash

Darren Duncan wrote:
>> 3. Suggestion, but it would be probably hard to implement: to make SQL
>> engine prefix each returned column with table alias. Of course it would
>> not be a default behavior, but it would be enabled by some session wide
>> setting.
>>
>> # SELECT * FROM c1, c2 WHERE c1.id=c2.id;
>> c1.id | c1.address | c2.id | c2.address
>> [...]
>> # SELECT * FROM c1 JOIN c2 USING (id);
>> ??id | c1.address | c2.address
>>
>> As JOIN returns only one copy of id, it would be hard to decide about
>> results (could return one copy for each alias like above).
>>
>> 4. Probably also hard to implement, something like:
>> # SELECT c1.* AS c1_*, c2.* AS c2_* FROM ...
>
> Some DBMSs already do this, and is a *bad* idea.

Actually, I should clarify that it is the top 2 examples that some DBMSs already
do, and that's a bad idea.

What you proposed in #4 looks unique and might actually be useful, that just
being a shorthand for mass regular AS renames.

But what would be *more* useful in general is if SQL supported an all-but
syntax, where you explicitly named the columns you don't want when that is a
shorter list. I know I've proposed this before.

-- Darren Duncan