CODEWITHSUNDEEP
sqlsqlite
Addev
Addev

Reputation: 32233

Select * and make an alias over one column

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)

Upvotes: 7

Views: 9065

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

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!

Upvotes: 4

maksimov
maksimov

Reputation: 5811

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

Upvotes: 8

Related Questions