Q
Problem solve Get help with specific problems with your technologies, process and projects.

Identify column names by table names when using SELECT *

I am joining multiple tables in a select statement and I want to add the tablenames to the column names so I can trace its origin. How can I do this and still use the *?

I am joining multiple tables in a select statement and I want to add the tablenames to the column names so I can...

trace its origin. For example:

select *
from A
left join B on B.ID=A.ID

This will return all columns of A and B, such as if A has Name and Address as its fields, and B has Name.

Name  Address    Name
data  datadata   data 

However, I would like to have the output as:

A.Name  A.Address   B.Name
data    datadata    data 

How can I do this and still use the *?

You cannot.

You can give each column an alias name, like this:

select A.Name     as A_Name
     , A.Address  as A_Address
     , B.Name     as B_Name
  from A
left outer
  join B 
    on B.ID = A.ID

   
A_Name  A_Address   B_Name
data    datadata    data   

However, you cannot do it any other way. Besides, you should not be using the dreaded, evil "select star" anyway. See Why "select star" is bad (9 March 2004).

This was last published in July 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close