Select * and make an alias over one column

Question

Lets imagine a join of 3 tables, it have 14 columns for example. I'd like to rename one of the columns with an alias. Is there some way or doing it without having to write the other 13 column names in the select statement?

What I'm looking in pseudocode is

SELECT * [rename user.login as username] from users join 
        (select * from statistics join accounts)

You should not use `select *` in production code anyway. I'm pretty sure that writing the 13 column names would haven taken less time than writing this question. — , Apr 18 '12 at 12:40

maksimov · Answer 1 · 2012-04-18T09:07:54.127

7

select users.login as username, users.* 
     from users

Unfortunately the column will appear twice, but there's nothing you can do.

With join it will look something like this:

select u.login as username, u.*, s.* 
     from users as u, statistics as s 
     where u.user_id = s.user_id

edited Apr 18 '12 at 09:07

answered Apr 18 '12 at 09:02

maksimov

5,792
1
30
38

"the column will appear twice, but there's nothing you can do" -- I suspect they want to rename the column in order to perform a natural join (note the absence of explicit join clauses), which would certainly solve the problem of the column appearing twice when using `SELECT *`. – onedaywhen Apr 18 '12 at 13:03

score 4 · Accepted Answer · edited May 23 '17 at 12:26

Your proposed syntax is a good one, IMO. In fact, it is very similar to the database language Tutorial D:

user RENAME ( login AS username )

would project all 14 attributes from the user relvar with one renamed as specified.

Similarly, Tutorial D has an ALL BUT projection operator e.g.

user { ALL BUT login }

would result in a relation of 13 attributes in your case.

Sadly, SQL has never these useful shortcuts and probably never will. Perhaps we should consider ourselves lucky to have got SELECT * in the early days; we would never be granted it in more recent times! The group representing SQL users took a proposal for a SELECT * BUT <commalist> type syntax to the SQL standard committee but it was rejected. SELECT * is disliked on SO too!

Select * and make an alias over one column

2 Answers2