Omit table name and dot in SELECT query

Question

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.*)

Answer 1

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.

Answer 2

Why not simply:

select 
  c1 
, 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:

select 
  table1.id
, 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:

select 
  a.id
, 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.