When I perform this query:

SELECT `tablename`.* FROM `something`.`tablename`

I get a table with column names that contain dots:

tablename.c1 | tablename.c2 | tablename.c3
     a       |       1      |     2
     b       |       1      |     3

I don't want this, I just want the column names c1, c2 and c3, I can solve this by writing the following query:

SELECT `tablename`.`c1` as `c1`,
       `tablename`.`c2` as `c2`,
       `tablename`.`c3` as `c3` FROM `something`.`tablename`

I however have many columns which makes it a very long query. How can I rename the columns from the first query or how can I get this right from the start?

(p.s. the query I'm using contains multiple table references that is why I specify the table name tablename.*)


Why not simply:

, c2 
, c3 
from tablename ; 

With only one table involved, the database does not need to be told from which table the field should be taken. If you were joining two tables that both contained, say, an id column, then you would have to tell the database (through the query) which one you wanted returned:

, table1.field1 
, table2.field2 
from table1 
inner join table2 
on ... 
where ... 

But even that's quite cumbersome. Enter "Correlation Names" - a shorthand naming, with each query, denoting which table is which:

, a.field1 
, b.field2 
from table1 a 
inner join table2 b 
on ... 
where ... 

Do not worry about lengthy field names or listing lots of fields in your queries - that's exactly what you should be doing - meaningful field names and only retrieving the fields that you need in each query.

Never use "select *" in Production code.


You will have to do it the way you proposed in your question.

In cases of many columns, you can get the code generated for you. I find string manipulation in Excel really useful for generating SQL code.

  • As the OP says in his question, "the query I'm using contains multiple table references that is why I specify the table name". – mustaccio Jan 17 '20 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.