SQL Aliasing

[RedBassett]

Greetings,

Is there a way in SQL to say SELECT tablename.*, such that each column in the result is named tablename.columnname?

- RedBassett

[Tylr]

You mean like this?

SELECT * FROM `tablename` WHERE `name` = 'columnname'

If not, could you explain a little more what you're trying to do?

[RedBassett]

Mar 20, 2012 19:41:21 GMT -8 Tylr said:

You mean like this?

SELECT * FROM `tablename` WHERE `name` = 'columnname'

If not, could you explain a little more what you're trying to do?

Apologies.

Table has three columns.

Column One

Column Two

Column Three

If I say "SELECT * FROM table", it will return the three columns as above, however if I say "SELECT `column_one` AS `table.column_one` (for each column), it will return:

Tablename.Column One

Tablenam.Column Two

Tablename.Column Three

I am trying to find a way to do the second action, using a SELECT * command, instead of manually entering each alias.

[Tylr]

Ah, I think I see what you're saying. What RDMS are you using? In MySQL, SELECT * automatically returns an array, so you don't have to worry about that. (Instead of Tablename.ColumnOne you'd get tablename['columnone'])

[RedBassett]

Mar 20, 2012 20:36:55 GMT -8 Tylr said:

Ah, I think I see what you're saying. What RDMS are you using? In MySQL, SELECT * automatically returns an array, so you don't have to worry about that. (Instead of Tablename.ColumnOne you'd get tablename['columnone'])

My issue is when I start using multiple tables. I need to label the values with their original tables.

[Chris]

Mar 20, 2012 21:34:15 GMT -8 RedBassett said:

Mar 20, 2012 20:36:55 GMT -8 Tylr said:

Ah, I think I see what you're saying. What RDMS are you using? In MySQL, SELECT * automatically returns an array, so you don't have to worry about that. (Instead of Tablename.ColumnOne you'd get tablename['columnone'])

My issue is when I start using multiple tables. I need to label the values with their original tables.

I don't know an automatic way, but you can manually do that...

SELECT column AS super_awesome_column FROM table1

[RedBassett]

Mar 25, 2012 10:02:15 GMT -8 Chris said:

Mar 20, 2012 21:34:15 GMT -8 RedBassett said:

My issue is when I start using multiple tables. I need to label the values with their original tables.

I don't know an automatic way, but you can manually do that...

SELECT column AS super_awesome_column FROM table1

I have been aliasing the columns manually so far, but I just wondered if there was a way to do prepend the table name without typing in each column name.

Worst comes to worst, I can always deal with long SQL queries. Probably going to have the names generated by code anyway.

- RedBassett

[BFD]

Mar 25, 2012 16:37:59 GMT -8 RedBassett said:

Mar 25, 2012 10:02:15 GMT -8 Chris said:

I don't know an automatic way, but you can manually do that...

SELECT column AS super_awesome_column FROM table1

I have been aliasing the columns manually so far, but I just wondered if there was a way to do prepend the table name without typing in each column name.

Worst comes to worst, I can always deal with long SQL queries. Probably going to have the names generated by code anyway.

- RedBassett

As a DBA, I am supposed to tell you to avoid using SELECT *, especially when accessing from code. Database servers run much better if they are only asked to return the columns that are needed. Experience says that SELECT * isn't all that evil - unless you are dealing with serverside objects (stored procedures, views, etc.).

To directly answer the question...I do not know of any DBMS (other than maybe Microsoft Access) where you can have a query automatically return an alias for individual columns. Building the query in code is the only way I know of.

[RedBassett]

Mar 27, 2012 15:02:25 GMT -8 BFD said:

Mar 25, 2012 16:37:59 GMT -8 RedBassett said:

I have been aliasing the columns manually so far, but I just wondered if there was a way to do prepend the table name without typing in each column name.

Worst comes to worst, I can always deal with long SQL queries. Probably going to have the names generated by code anyway.

- RedBassett

As a DBA, I am supposed to tell you to avoid using SELECT *, especially when accessing from code. Database servers run much better if they are only asked to return the columns that are needed. Experience says that SELECT * isn't all that evil - unless you are dealing with serverside objects (stored procedures, views, etc.).

To directly answer the question...I do not know of any DBMS (other than maybe Microsoft Access) where you can have a query automatically return an alias for individual columns. Building the query in code is the only way I know of.

Thank you.

As for the SELECT *, I have code that has a number of objects, each representing a website object (such as a user account). The SELECT * is to load the table from the DB into that object.

- RedBassett

[Bennett 🚀]

SELECT * is a very lazy way of getting stuff from a database and is a bad habit to get into. I myself do this all the time, but you should always select only what you need to save one load times & query time.

[RedBassett]

Mar 27, 2012 17:34:47 GMT -8 Bennett 🚀 said:

SELECT * is a very lazy way of getting stuff from a database and is a bad habit to get into. I myself do this all the time, but you should always select only what you need to save one load times & query time.

Ok. As of this aliasing issue, I have been forced to start selecting everything manually. I actually updated the aliasing thing, so I don't have to use it in my code, I can do a standard select, but I will be forcing myself to use column names from here on out.

[Tylr]

Mar 27, 2012 17:34:47 GMT -8 Bennett 🚀 said:

SELECT * is a very lazy way of getting stuff from a database and is a bad habit to get into. I myself do this all the time, but you should always select only what you need to save one load times & query time.

How about if your table has ~1000 rows and ~10 columns? At what point does selecting specific columns actually make a difference?

[BFD]

Mar 27, 2012 20:23:06 GMT -8 Tylr said:

Mar 27, 2012 17:34:47 GMT -8 Bennett 🚀 said:

SELECT * is a very lazy way of getting stuff from a database and is a bad habit to get into. I myself do this all the time, but you should always select only what you need to save one load times & query time.

How about if your table has ~1000 rows and ~10 columns? At what point does selecting specific columns actually make a difference?

EDIT TO DIRECTLY ANSWER:It really depends on the data type and the contents. Select * really hurts in SQL Server when you have a Text or Binary column that is used to store large amounts of data. I have seen where removing a single binary column from a query selecting a few hundred rows reduces execution from ten minutes plus to almost instant. And if you are returning your data as xml, even columns with small amounts of data can really bloat your return results.

Aliasing 10 columns is not that difficult...and if you always alias, then you don't have field name conflicts when you join in additional tables (foreign key column names should match the primary key column name whenever possible).

A good rule of thumb is to always alias column names when you are joining multiple tables...and if a single table has enough columns to where aliasing gets cumbersome, you should consider further normalization.