How to SELECT * and rename a column?

Question

I can easily select all my columns from a table with:

SELECT * FROM foo

But if I want to rename the column bar in foobar, I need to specify all the fields manually. This make my query not robust if a new column is added to the table:

SELECT a,b,c,d,bar AS foobar FROM foo

Is there a possibility to write something like

SELECT *, bar AS foobar from foo

@RaduGheorghiu nothing wrong with using select *. Its maybe not optimal in all cases but saying one should NEVER use it just wrong. — Jakob, Jul 10 '15 at 14:02
@RaduGheorghiu, why? Isn't a good way to be protected if the database is updated? — nowox, Jul 10 '15 at 14:03
@nowox Using `SELECT *` is a surefire way to *break* a script if the table is updated... — Siyual, Jul 10 '15 at 14:04
@Jakob Well, I wish I can do this. I cannot touch the structure of my horrible database :( — nowox, Jul 10 '15 at 14:04
Select * is fine for debugging, testing, evaluation purposes or information gathering as far as embedding it in production code, I wouldn't recommend it...-->http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select If you need all the columns of the table query the information_schema to get the listing so you don't have to retype them all. — xQbert, Jul 10 '15 at 14:04
You mean "This makes my query robust if a new column is added to the table"! — jarlh, Jul 10 '15 at 14:05
Here are some ideas as to [why you shouldn't use `SELECT *`](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) — Radu Gheorghiu, Jul 10 '15 at 14:11
@Jakob you are right, i'll clean up my comments. I suggest you do the same. — Radu Gheorghiu, Jul 10 '15 at 14:13
@Jakob To a rough approximation, one should never use `SELECT *` in a production environment. — Strawberry, Jul 10 '15 at 14:13
How about building the query dynamically by fetching the list of columns from system tables and preparing the SELECT statement? In this case you can add aliases to columns dynamically and build the result-set you need. — Ispirer SQLWays Migrations, Jul 10 '15 at 14:25
Not sure, at list I can put all the logic into the single sp where I can pass the table name, schema name, the list of source columns that need to be changed and the corresponding list of columns with the alias names. — Ispirer SQLWays Migrations, Jul 10 '15 at 14:37

score 11 · Accepted Answer · edited Jul 25 '15 at 16:26

11

Yes you can do the following:

SELECT  bar AS foobar, a.* from foo as a;

But in this case you will get bar twice: one with name foobar and other with bar as * fetched it..

edited Jul 25 '15 at 16:26

potashin

44,205
11
83
107

answered Jul 10 '15 at 14:00

Aman Aggarwal

17,619
9
53
81

You answer is nice but as you said I will get the column twice. So I guess I will wait a bit for another answer – nowox Jul 10 '15 at 14:06
1

there is no other possibility as * always fetch all the columns.. and in that case you can't change name in simpler queries.. if you are going to use some case or anything else then it will slow down.. – Aman Aggarwal Jul 10 '15 at 14:07

score 2 · Answer 2 · answered Jul 10 '15 at 14:06

There's not really a great way to get at what you're after. Typically, best practices dictate that you should avoid SELECT * ... for performance reasons. Especially for the reason you just specified - say you add a large field to this table that is irrelevant to the query at hand. You're still retrieving the data with a "*" whether you need it or not.

As for your query, you can do SELECT *, bar AS foobar from foo - but you'll be duplicating your results, as you'll return the original column by name as well as a column with your new alias.

Hope this helps!

score 0 · Answer 3 · answered Jul 10 '15 at 14:26

0

Normally most table colums are not important for a select statement. I had different cases in the last 10 years and most of 60% oft colums were unused.

In our times performance is one of last challenges for developers.

Please test in every case with Explain MySQL command for performance of statements.

answered Jul 10 '15 at 14:26

cHillb3rT

9
2

1

This should be a comment, not an answer. – Jakob Jul 10 '15 at 14:41

How to SELECT * and rename a column?

3 Answers3