joining tables with some col names the same

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

joining tables with some col names the same

Author

Topic

bill_
Starting Member

38 Posts

Posted - 2013-12-13 : 10:28:00

If you need to inner join 2 tables that have some columns names that are the same, how can you have those columns be named differently in the query result without aliasing them individually?

Tried select a.*,b.* from tbldm a,tblap b where a.id=b.id hoping the col names in the result would have the a.s and b.s in front of them but they didn't.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-13 : 10:56:52

The columns in the result set will always be the same name as the source column. So if you want them to be named differently you must alias them:
a.<column> as [<alias>]

And of course if your column is an expression the column will be unnamed unless you specify an alias.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2013-12-14 : 00:57:54

Better never to use SELECT *

You get all columns ... but that is all columns both now, and in the future too. Better to only select the columns that your application needs now. Otherwise if you add, say, a huge text column to that table in the future then that new column will be included in your SELECT * statement and it will cripple the performance because every SELECT * query that uses that table will pull back every column - including the new massive text column (and you'll then have to change every SELECT * statement all over your code to fix it).

There may appear to be exceptions - e.g.


CREATE VIEW MyView
AS
SELECT A.*,
       B.*
FROM MyTableA AS A
     JOIN MyTableB AS B
         ON A.ID = B.ID

but I still think that even such obvious cases should have a full column list so that if a new column needs to be added in the future the implications for any code using that VIEW can be considered.

Otherwise SELECT * is just out-of-sight and, sadly, out-of-mind too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources